Skip to Content
0

Select from a table ignoring spaces

Oct 28, 2016 at 07:24 AM

114

avatar image

I want to select from ADRC to find customers who have a particular phone number.

The phone numbers are stored in ADRC with spaces in different places e.g. 012345 or 012 345 or 0123 45 etc.

Is there a way to select from ADRC with the input phone number 012345 and for it to return all customers with 012345 or 012 345 etc.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Best Answer
Mike Gross Oct 31, 2016 at 08:52 AM
1

Even better solution : ADR2 has the field TELNR_CALL which stores numbers without spaces. So I can use that!

Share
10 |10000 characters needed characters left characters exceeded
Matthew Billingham
Oct 28, 2016 at 08:41 AM
2

Use ADBC to issue native SQL to the database with replace - https://help.sap.com/saphelp_hanaplatform/helpdata/en/20/e66a2c75191014bac6a279955db665/content.htm

. E.g. SELECT ... WHERE replace(TEL_NUMBER,' ','') = phone_number_without_spaces

Show 3 Share
10 |10000 characters needed characters left characters exceeded

But only if he's under Hana, right?

0

Not only Hana, some other database, like Oracle for example, support the REPLACE function (also the REGEXP_REPLACE function too) So you could use ABDC classes like CL_SQL_STATEMENT.

From 7.51 (or less?) you can also use the REPLACE function in OPEN-SQL, read ABAP News for Release 7.51 – Case Insensitive Search in SQL and Other New Functions from Horst Keller.

Regards,
Raymond

3

Thanks for the explain, Raymond.

My old Sybase with 731 cannot support it, i just tried :|

0
Simone Milesi Oct 28, 2016 at 08:29 AM
1

I do not think it's possible to do via a single select (at least not before 740, but i did not used too much 740 so i speak for previous releases).

My suggestion is to select into an internal table KUNNR - Phone Number and then work with the internal table: loop it, condense no gaps the phone number, and the read the table for your give phone number.
I do not think performances are so highly impacted by this approach (not clean but surely works)

Share
10 |10000 characters needed characters left characters exceeded
Kali Charan Oct 28, 2016 at 08:27 AM
-1

Any possibility with Like operator in Select query .

Else.

Each time move the 012345 to any string and remove the Space and
now check it

If 012345 eq 012345
append the record to the table.

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Given Phone number "02 256432": How can you use LIKE?

0

Ok, That's what i mentioned any possibility (some doubt).

0
Mike Gross Oct 28, 2016 at 01:55 PM
0

Thanks everyone for the suggestions.

We have decided to load all customers/phone numbers into a Z table without spaces. A BADI will populate this table when any phone numbers are created or changed.

The retrieval of the customers based on the phone number needs to be fast which is why we have gone down this route.

Share
10 |10000 characters needed characters left characters exceeded