function separate string

Question

I have this question for you.I am working a function which has the whole address in one field.I am trying to separate it.I have started working on the one below and I am having difficulty when I try to work on the zip ,I want to test if there is a zip first at the end and if so I am trying to separate it from the state.Could you please have alook at it?Thanks alot guys as usual I appreciate you support..

      declare @var1 varchar(100)='1234 S.Almeda way,Seattle,WA9810'--just an example
        ,@u int
        ,@r int
        ,@var2 varchar(100)
        ,@var3 varchar(100)
        ,@Zip varchar(25)
        ,@var4 varchar(100)=null
             set @u = charindex(',', @var1)
             set @var2=rtrim(ltrim(substring(@var1, @u+1, 999)))
             set @r=CHARINDEX(',',@var2)
             set @var3=rtrim(ltrim(substring(@var2, @r+1, 999)))

             --set @var4=RIGHT(@var3,5)--not enough
             if (len(@var3)>=5 and ISNUMERIC(@var3)=1 )
             set  @var4=RIGHT(@var3,5)
             set rtrim(substring(@var3,1,len(@var3)-5))
             else set @var4=''

RichardTheKiwi · Accepted Answer · 2012-11-07 20:13:42Z

1

Here's some sample code you can merge into yours

declare @var1 varchar(100)='1234 S.Almeda way,Seattle,WA9810'--just an example
declare @lastcomma int = len(@var1) - charindex(',', reverse(@var1)+',')
declare @lastPart varchar(100) = substring(@var1, @lastcomma+2, 100)
select @lastPart 
declare @zipstart int = patindex('%[0-9]%', @lastpart)
declare @zip varchar(5) = ''
if @zipstart > 0
    select @zip = substring(@lastpart, @zipstart, 5), @lastPart = rtrim(substring(@lastpart,1,@zipstart-1))
select @lastpart, @zip

answered Nov 7, 2012 at 20:13

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mntne Dav Over a year ago

Hi @RichardTheKiwi,I tested it, the logic works great.I am going to incorporate with the rest and test it again,will notify you if it fails.I dont think it will fail though.But once again I really appreciate your great answers.Thanks.

jazzytomato · Accepted Answer · 2012-11-07 20:10:45Z

0

You are obvioulsy looking for a split function, which is not built-in SQL Server.

I've stop reading your code when I saw the name you give to your variables (really bad choices, it should have some kind of meaning)

There are many ways to implement it, I'll pick one randomly from a google search (nah, I'm not ashamed, I don't want to reinvent the wheel)

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))       
returns @temptable TABLE (items varchar(8000))       
as       
begin       
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return       
end

use it this way :

select top 10 * from dbo.split('1234 S.Almeda way,Seattle,WA9810',',')

It'll give you a column with a result in each row

SOURCE : http://blog.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx

You'll find plenty of other example with a quick web search. ;)

answered Nov 7, 2012 at 20:10

jazzytomato

7,2593 gold badges33 silver badges44 bronze badges

1 Comment

Mntne Dav Over a year ago

Hi @Thomas,thanks for sending me the above function.I checked it, it works great.But here is the situation ,the zipcode is attached to the state, its not separated with a comma,that is the one which I am facing difficulty with.BTW var2 is city,var3 is state,var4 is zip.Once again I am very thankful...

Collectives™ on Stack Overflow

function separate string

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related