SQL400 Strip non-numeric from a column

60 pts.
Tags:
AS/400
non-numeric
SQL/400
Striping
Hello everyone,
Is there a way to strip non-numeric characters from a char column? Example: I have a Clients file, with column "Telephone Number" (CLPhone, 20, A). The thing is, phone number for many clients is filled with different formats.
Example1: (123-12345) Example2: 123 12345 Example3: 123/12345
And even worse :). Is there a way to select only the numeric value? like in:
Select ClientID, ClientName, StripChars(CLPhone) From Customers
Is there a direct stripping function?
Thanks a ton


Software/Hardware used:
AS400
ASKED: January 16, 2012  6:54 AM
UPDATED: March 17, 2012  5:35 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

What type of format do you want it in? You might be able to use the strip, replace:

SELECT REPLACE(clphone, ‘/’, ‘-’) (field, character being replaced, replace with)
FROM Customers

You would need to do several of these depending on the characters being changed. If there is a blank and you know the position you could add the substring feature.

[kccrosser] Builder400 – you are on the right track with the use of a function. There is a Replace function built-in, which is fine for replacing *known* characters. However, if your source data contains characters for which you didn’t code, then the output data will still have the non-numeric characters.

Instead, create a simple function that takes a string and returns a string, like (VB pseudocode – I don’t work on AS/400 systems):

<pre>function StripChars ( sInstring as string ) as string
i as Integer
sOutString as string

sOutString = “”
for i = 1 to len(sInString)
if substring(sInString, i, 1) in (’0′,’1′,’2′,’3′,’4′,’5′,’6′,’7′,’8′,’9′) then
sOutString = sOutString & substring(sInString, i, 1)
next ‘ i
StripChars = sOutString
exit function

end function</pre>
Basically, instead of trying to remove the non-numerics, the easy approach is to detect and keep the numerics.

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Experiment with this:
    SELECT
     MyCol,
     translate( xor(MyCol, translate(MyCol, x'00000000000000000000', '0123456789')), ' ', x'00')
     FROM MyTable
    Assuming that MyTable has a column named MyCol and that MyCol is a character column that has at least one numeric digit but mixed with non-numeric characters, the result column should contain just the numeric digits. Now, there may be leading, trailing or embedded blanks in the result. It's not clear what you are actually trying to achieve. Perhaps after viewing results, you can provide more examples that can narrow things down. Tom
    125,585 pointsBadges:
    report
  • Builder400
    Thank you guys, Dear Kccrosser, I think I need to clarify my request more: In fact I do not need to strip the non-digits, in the (Select strip(phone) from clients), but I want to strip in in the (Where) part. The thing is the clients file is HUGE, and we want an ability to search clients by phone number. so if I want to read the file sequentially and use a function (example StripChars) it will take a long time to fetch the results, instead I want to show to fetch the valid clients directly through an SQL statement: The search input field will be numeric only: so the user would enter something like: 12312345_______ And the task is to return the valid clients with the matching phone number: Select clientNumber, client name, clientphone from clients Where stripChars(clientPhone) = theEntryItem Dear TomLiotta, Thanks, your solution seems the perfect thing for me, but after trying it it didn't return the valid results. I think I might have messed up with some parameters I will try it again and return back to you. Thanks again guys.
    60 pointsBadges:
    report
  • Builder400
    Here are my results: 1- For this Statement:
    Select PhoneNumber, 
    Xor(PhoneNumber, Translate(PhoneNumber, x'000000000000000',  
    '0123456789'))                           
    from Clients
    Result is:
    1002104           1002104 
    00021542          00021542
    742/1             742 1   
    1001523           1001523 
    F6652085           665205لا
    
    2- For this Statement:
    Select PhoneNumber,                                                
    Translate(xor(PhoneNumber, Translate(PhoneNumber, x'000000000000000',  
    '0123456789')), ' ',x'00')                    
    from Clients
    Result is:
    1002104           1002104 
    00021542          00021542
    742/1             742 1   
    1001523           1001523 
    F6652085           665205لا
                                                        
    
    For a strange reason, number 8 is translated into something else! True invalid characters are replaced with a space. Now I'll replace it with blank.
    60 pointsBadges:
    report
  • TomLiotta
    For this part of my example statement: translate( MyCol, x'00000000000000000000', '0123456789' ) ...be careful in the length of the hex string. It is 20 zeros. This function converts each of the character digits 0-9 to binary zeros. In your example statements, you only show 15 zeros. I'd have to look up the definition of a hex string with an odd number of hex digits in DB2 SQL, but that would mess up the digits 8 and 9 at the very least. What that part does is create a temporary result that masks all of the digits with binary zeros. That temporary value is then exclusive-ORed with the original string. And the temporary result of that is a new temporary string that retains all of the original numeric digits while converting the non-digits to binary zeros. (But note that the hex string must represent the full length of the ten possible digits from '0' through '9', so it must be twenty characters long.) And finally, the outermost TRANSLATE() in my example converts any remaining bytes of binary zeros to blanks. The final result should come out as just the original numeric digits with the non-digits as blanks. Try the statement with the correct length for the innermost hex string. If it does work on your system, the final problem will be to condense spaces out of the result. That'll be much easier if we can get it to the point of having only the acceptable digits. Tom
    125,585 pointsBadges:
    report
  • Builder400
    Thanks a lot Tom, Actually, the actual size of the field is 15 not 20 as I stated in the example of the question, and I thought x'15 zeros' would do the job forgetting that it's a hex value so I added 4 more zeros (so now it is x'20 zeros') and the 8 and 9 did actually show perfectly. (I tried adding two more zeros and the 8 showed but the 9 didn't, added two more and the 9 shows as well). Now the problem remains with the binary zeros, they do not seem to be trimmed even with the outermost Translate function.
    60 pointsBadges:
    report
  • Builder400
    Thanks a lot Tom, Actually I managed to fix the issue using your excellent suggested solution, adding some extra Replace function. Here's the final formula:
    Select Replace(Translate(
    	xor(PhoneNumber,Translate(PhoneNumber, x'00000000000000000000','0123456789'))
    	, 'x', x'00'), 'x', '')                 
    from Clients                                             
    
    60 pointsBadges:
    report
  • Sloopy
    Using a file on my system, I have this for the WHERE clause. I have to keep the search value as a character string, which is easy enough for you to also do:
    select * from cust                                                        
    where locate('1982690', Replace(Translate( xor(CSPHON, Translate(CSPHON,  
                            x'0000000000000000000000000000000000','0123456789'
                            )),'x', x'00'), 'x', '') ) > 0
    
    I do like the translate logic, Tom, and the replace too. Lovely meothods to steal!
    2,195 pointsBadges:
    report
  • TomLiotta
    If you have statement logic that works for your WHERE clause, it would be interesting to see it. I thought about it after my last comment and had a thought about how to implement it. Can you post your final WHERE clause? It seems counter intuitive to start the whole thing off by getting rid of the digits, but it was the first thing that came to mind as a way of figuring out (in SQL logic) what and where the non-digits were. As long as it's just an intermediate result, it didn't seem to be troublesome. And I think that TRANSLATE() and XOR() could be reasonably quick. Tom
    125,585 pointsBadges:
    report
  • Kccrosser
    If the objective is to find possible phone numbers, then the simplest way would be to use the LIKE clause and wild-card the input search string. For example, to find 754-1235, use: ...LIKE '%7%5%4%1%2%3%5%' If your search string is in a known phone number format and the data in the database has at least some rational formatting, you can make the search more intelligent, ala: ...LIKE '%754%1235%' or ...LIKE '%800%123%4567%' These will quickly/easily find all the source data rows containing those sub-strings. If you want to fully exclude non matches, then I would nest a couple of queries, and use a function on the SELECT clause to get the true matches, e.g.:
    declare @searchPattern varchar(32);
    declare @wildCardPattern varchar(64);
    set @searchPattern = '8001234567';
    set @wildCardPattern = '%8%0%0%1%2%3%4%5%6%7%';
    SELECT
       MyIsMatchFunction( tmp,Phone, @searchPattern) as IsMatch,
       tmp.Phone
    FROM
       (SELECT Phone FROM SourceTable WHERE Phone LIKE @wildCardPattern) tmp
    WHERE
       IsMatch = 1;
    3,830 pointsBadges:
    report
  • TomLiotta
    …LIKE ‘%7%5%4%1%2%3%5%’ That is potentially a good solution. If this is embedded SQL, it will take a little care in creating the SQL string. It could also catch cases where the number had some kinds of typos from the original data entry. For example, if the original entry duplicated one of the digits, e.g., '754-12335', it would still be found. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following