topic Re: select with spacing in Application Development and Automation Discussions

select with spacing

Former Member — Thu, 13 Sep 2007 13:22:26 GMT

Hi all,

I need to do a SELECT statement on a field, but the field may contain spaces

e.g.

IBCU354355-1 could be IBCU 354355-1 or IBCU354355 - 1 etc...

Is this possible

Thanks

Re: select with spacing

Former Member — Thu, 13 Sep 2007 13:24:07 GMT

Hi,

Before selecting the data, you can use the CONDENCE statment to remove the spaces, then use the varibale to get the data

Regards

Sudheer

Re: select with spacing

Former Member — Thu, 13 Sep 2007 13:25:04 GMT

Use LIKE and wildcard % in your where clause.

SELECT...

WHERE <fieldname> LIKE 'IBCU%354355%'

Re: select with spacing

Former Member — Thu, 13 Sep 2007 13:25:34 GMT

select the field......then move it to charecter type variable......then use CONDENSE <> NO-GAPS.

to remove spaces.

Regards

Vasu

Re: select with spacing

Former Member — Thu, 13 Sep 2007 13:29:07 GMT

Hi,

You can use CS command

c1 contains the character string c2.

Trailing blanks in c1 and c2 are ignored if the respective field is of type C.

An empty string c2 (i.e., only blanks with type C, or empty string with type STRING) is included in any string c1, including the empty string itself. On the other hand, there is no non-empty string c2 included in an empty string c1.

If the result of the comparison is positive, the system field SY-FDPOS contains the offset of the first character of c2 in c1.

If the result of the comparison is negative, the system field SY-FDPOS contains the length of c1.

The comparison is not case-sensitive.

In select query you can write:

where field CS sapce.

Hope it works.

Thanks,

Sandeep.

Re: select with spacing

Former Member — Thu, 13 Sep 2007 13:52:56 GMT

Yes, it is possble.

For example - When you select Material Description( MAKT-MAKTX), it may contain space, special chars etc. But you can see them it reports. Don't you ?

Re: select with spacing

Former Member — Thu, 13 Sep 2007 14:00:16 GMT

CONDENSE would not work as then it will look for the condensed "no spaces" version in the table and would not select the ones with the spaces any more.

The LIKE % would work if I could guarantee that in that position there would be a %.

what i was thinking of doing is doing a replace

replacing spaces with % and then doing a like command...

What do you think?

Re: select with spacing

Former Member — Thu, 13 Sep 2007 14:03:30 GMT

% is a true wildcard, so it will still work even if there is nothing in that space.

For example, IBCU%354 would find both IBCU354 and IBCU 354

Re: select with spacing

Former Member — Thu, 13 Sep 2007 14:09:00 GMT

> % is a true wildcard, so it will still work even if

> there is nothing in that space.

> For example, IBCU%354 would find both IBCU354 and

> IBCU 354

I agree, but it wouldnt find

IBCU354 1234 because space is in different location.

Re: select with spacing

Former Member — Thu, 13 Sep 2007 14:14:15 GMT

> Use LIKE and wildcard % in your where clause.

> SELECT...

> WHERE <fieldname> LIKE 'IBCU%354355%'

I need to build up the field like this - but wont know where the spaces could occur.

That is the problem

Re: select with spacing

Former Member — Thu, 13 Sep 2007 14:18:13 GMT

In that case then you will have to select the field first and use CONDENSE <fieldname> NO-GAPS to remove the spaces.

Re: select with spacing

Former Member — Thu, 13 Sep 2007 14:18:21 GMT

Depending on your database, you <u>may</u> be able to achieve this with native SQL. Check with your DBAs to see if they know of any syntax for this.

Rob

Re: select with spacing

Former Member — Thu, 13 Sep 2007 14:22:10 GMT

thanks for the replies...

I think I will just loop through the field and check each item if its a space convert it to %.

How does that sound

Re: select with spacing

Former Member — Thu, 13 Sep 2007 14:26:29 GMT

What if the text contains an actual '%'?

Rob