topic Select from a table ignoring spaces in Application Development and Automation Discussions

Select from a table ignoring spaces

mgross1 — Fri, 28 Oct 2016 07:24:44 GMT

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.

Re: Select from a table ignoring spaces

former_member196331 — Fri, 28 Oct 2016 08:27:45 GMT

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.

Re: Select from a table ignoring spaces

SimoneMilesi — Fri, 28 Oct 2016 08:29:02 GMT

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)

Re: Select from a table ignoring spaces

SimoneMilesi — Fri, 28 Oct 2016 08:29:59 GMT

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

Re: Select from a table ignoring spaces

matt — Fri, 28 Oct 2016 08:41:11 GMT

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

Re: Select from a table ignoring spaces

SimoneMilesi — Fri, 28 Oct 2016 08:54:18 GMT

But only if he's under Hana, right?

Re: Select from a table ignoring spaces

former_member196331 — Fri, 28 Oct 2016 09:06:18 GMT

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

Re: Select from a table ignoring spaces

RaymondGiuseppi — Fri, 28 Oct 2016 11:06:47 GMT

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

Re: Select from a table ignoring spaces

SimoneMilesi — Fri, 28 Oct 2016 11:18:46 GMT

Thanks for the explain, Raymond.

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

Re: Select from a table ignoring spaces

mgross1 — Fri, 28 Oct 2016 13:55:44 GMT

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.

Re: Select from a table ignoring spaces

mgross1 — Mon, 31 Oct 2016 08:52:29 GMT

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