09-22-2015 07:25 PM
Hi friends, can someone please help on SQL,
is there a way in SQL to query this?
we need 10 digit numbers "business_code" starting with real number (1 to 9) but we are facing leading uncertain zeros and we need to delete them. "business_code" is 10 digit so we need to get only 10 digit code like;
09-23-2015 01:19 AM
i am using PG code but getting this warning message and output is like this;
WARNING: In a call to the FINDC function or routine, the modifier "K" not valid.
09-23-2015 04:40 AM
I think slchen's code is a good option. With a Perl-Expression you can kick out all the leading and trailing 0's while keeping the ones in the middle.
Input business_code $50.;
Create Table Want As Select *, PRXChange("s/(^0+|0+$)//",-1,Strip(business_code)) As BC_New From Have;
09-23-2015 04:40 AM
The idea of PG i think that is to find the first digit not 0, to select the following 10 digits, so, "K" modifier doesn't exist, you could specify to search numbers from 1 to 9
findc(bcString, "0", "K")
09-23-2015 11:09 AM
In the mode of "an ounce of prevention is worth a pound of cure", why does the data source have extra digits? If some system is not complying with the planned data layout/contents for this field, how many others may have less subtle issues? If this can be addressed at an earlier point then you may not have to worry about the times when you start getting unexpected characters in the middle of your "10 digit" code like 000123-4567888 .
09-23-2015 12:05 PM
Thanks a lot everyone for your input, this is really so informative. i am sorry but i forgot to mention that i am using sas 9.1 where lot of options would have unavailable like PG option.
Another thing is i have used Slchen solution and it worked so thanks a lot....
Also, Balladrw - i understand your point but sometime things goes beyond our ablility to interfere and i think that's why we have experts like you all....
Once again thanks a lot all...
Need further help from the community? Please ask a new question.