How to capture whithout space data.

[#1]

Hi Community,

Please refere below data sample -

id

230

          123

455

           555

645

222

i only want those data that i have no space so which function i have to used in script level.

my expected output is

id           

230                   

455          

645

222

Thanks in advance

[MK_QSL]

Trim(id) as id

Updated : I misunderstood your requirement...! LOL

[sunny_talwar]

May be make use of this SET statement

SET Verbatim = 1;

and then check the field for Space -> Where Len(Trim(KeepChar(FieldName, ' '))) = 0; Where Len(KeepChar(FieldName, ' ')) = 0;

[settu_periasamy]

May be

Load id from source where istext (id);

[sunny_talwar]

Sample attached:

SET Verbatim = 1;

Table:

LOAD id

FROM

Community_203223.xlsx

(ooxml, embedded labels, table is Sheet1)

Where Len(KeepChar(id, ' ')) = 0;

[Anonymous]

may be like this at script?

if(left(ID,1)<>' ', ID) as ID

[sunny_talwar]

Actually you might not even need SET Verbatim = 1;

Table:

LOAD id

FROM

Community_203223.xlsx

(ooxml, embedded labels, table is Sheet1)

Where Len(KeepChar(id, ' ')) = 0;

[Anonymous]

MRKachhiaIMP

I think it will remove spaces and display the values as well.

But he dont want to display those values, If I understood it correctly

[HirisH_V7]

Hi,

like this,

SAMPLE:
LOAD ID,
rtrim(ltrim(ID)) as ID1
FROM
[Trim Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

output:

PFA,

Hirish

[anlonghi2]

Hi Ishwar,

another solution could be:

set Verbatim=1;

LOAD id

FROM

example.xlsx

(ooxml, embedded labels, table is Sheet3) where SubStringCount(id,' ')=0;

Best regards

Andrea