| Author |
 Topic  |
|
|
dzhang
Starting Member
2 Posts |
 Posted - 08/31/2005 : 09:31:07
|
Hi ,
I am working on a stored proc to export data from a SQL Server table to a flat file. We have a table field with the following possible value:
122345684XT
23339034300-XT
423432424523242332X
422222222111111111232
423842389034209XYZ
I need to split these strings into two output fields(number and alpha)in SELECT statement. That is:
122345684XT -> 122345684 and XT
23339034300-XT ->23339034300 and XT
423432424523242332X ->423432424523242332 and X
422222222111111111232 -> 422222222111111111232
423842389034209XYZ -> 423842389034209 and XYZ
The length of whole string, the nemeric part and alpha character part are not fixed, but the maximum length of the whole string is nvarchar(25). Also, alpha characters are always at the end of the string. That is there is no such data:
23444444444444XY243234Z
The only possible way I can think is to use Regualar Expression. But my client doesn't like to use it.
Does anyone know how to do it? Thanks a lot for your kind help.
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
|
|
dzhang
Starting Member
2 Posts |
Posted - 08/31/2005 : 10:25:51
|
Thanks a lot for the reply. Yes, the function in the post above works perfectly for me! For other developers' reference, there are different functions defined in the above post which can do the same thing. Below is the one I take. Also, just make a little change (replace 0-9 with a-z) you can get only the alpha characters out!
if object_id('dbo.fnNumbersFromStr') > 0
drop function dbo.fnNumbersFromStr
go
create function dbo.fnNumbersFromStr(@str varchar(8000))
returns varchar(8000)
as
begin
while patindex('%[^ 0-9]%',@str)>0
Set @str = replace(replace(replace(rtrim(ltrim(replace(@str,substring(@str,patindex('%[^ 0-9]%',@str),1),''))),' ',' þ'),'þ ',''),'þ','')
return @str
end
go
|
 |
|
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 08/31/2005 : 14:55:03
|
yeah... after I wrote that for the thread, I added one to our library here at work that takes a parameter in the form of a like character comparison...
so it looks like:
Select dbo.GetCharacters('This is the 1st test string.','0-9')
returns '1'
Select dbo.GetCharacters('This is the 1st test string.','a-z')
returns 'Thisisthestteststring'
Select dbo.GetCharacters('This is the 1st test string.',' a-z')
returns 'This is the st test string'
its quite handy actually 
Corey
Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now." |
|
|
|
VIG
Yak Posting Veteran
Israel
86 Posts |
Posted - 08/31/2005 : 16:00:42
|
declare @t table(s varchar(25))
insert @t
select '122345684XT' union
select '23339034300-XT' union
select '423432424523242332X' union
select '422222222111111111232' union
select '423842389034209XYZ' union
select 'ABC'
select
left(s,patindex('%[^0-9]%',S+' ')-1 ) nbr
,right(s,len(s)-patindex('%[^0-9]%',S+' ')+1) alpha
from @t |
|
|
|
livemaxx
Starting Member
United Kingdom
1 Posts |
Posted - 01/22/2013 : 09:57:46
|
Hi VIG,
i am afraid your query fails to split text from numbers when a value has text character before the numerics.
TEXT07785621231 would not split as
alpha: TEXT
num:07785621231
in your solution |
|
|
| |
Topic |
|
Split alpha and numeric number
