Select from a table ignoring spaces

[mgross1]

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.

[mgross1]

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.

[former_member196331]

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.

[SimoneMilesi]

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

[former_member196331]

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

[SimoneMilesi]

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)

[matt]

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

[SimoneMilesi]

But only if he's under Hana, right?

[RaymondGiuseppi]

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

[SimoneMilesi]

Thanks for the explain, Raymond.

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

[mgross1]

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.

[mgross1]

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