split the string and store it in three different variables in sql server

Question

I am using below function to split the string into multiple strings everything is working fine but i need to store the results into multiple variables

instead of that the results are storing into a table

this is my query

DECLARE @strngLen int
DECLARE @split TABLE(siteId VARCHAR(100))

DECLARE @city varchar(100), @state varchar(100), @zip varchar(100)

SET @siteIds = 'OMAHA  NE  68117'

SET @strngLen = CHARINDEX('  ', @siteIds)

WHILE CHARINDEX('  ', @siteIds) > 0
BEGIN
    SET @strngLen = CHARINDEX('  ', @siteIds);

    INSERT INTO @split
    SELECT SUBSTRING(@siteIds,1,@strngLen - 1);

    SET @siteIds = SUBSTRING(@siteIds, @strngLen+1, LEN(@siteIds));
END

INSERT INTO @split
SELECT @siteIds

SELECT * FROM @split

---Here i need to store the @siteIds into multiple local variables 
---like in (@city varchar(100), @state varchar(100), @zip varchar(100))

What are you asking? How to split a string (dozens of faster duplicates) or how to write the SELECT @thisvariable=... statement ? How about the standard answer - don't try parsing on the server? Splitting will fail if a name contains spaces — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 19, 2017 at 8:36
I think his question is how to put the result in a different variable on each iteration — Romano Zumbé
– Romano Zumbé, Commented Jun 19, 2017 at 8:37
You can directly assign the characters before the first space as City to city variable and so on if the string is going to be in the same order. — Coder1991
– Coder1991, Commented Jun 19, 2017 at 8:38

Yared · Accepted Answer · 2017-06-19 13:46:19Z

4

if they are always in the same order and same separator then there is not need for a loop, this will do all the job:

DECLARE @siteIds varchar(100)
DECLARE @city varchar(100), @state varchar(100), @zip varchar(100)

SET @siteIds = 'OMAHA  NE  68117'

select @city=(parsename(replace(@siteIds,'  ','.'),3)),
       @state=(parsename(replace(@siteIds,'  ','.'),2)),
       @zip=(parsename(replace(@siteIds,'  ','.'),1))

edited Jun 19, 2017 at 13:46

Yared

2,4751 gold badge24 silver badges31 bronze badges

answered Jun 19, 2017 at 8:55

Jayvee

10.9k3 gold badges35 silver badges43 bronze badges

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

Comments

Coder1991 · Accepted Answer · 2017-06-19 08:53:01Z

0

If there is going to be only 3 strings (cit,state and zip) then the below select will fetch you the result.

declare @str1 varchar(10)
declare @str2 varchar(10)

select @city= substring(@siteIds,1,charindex('  ',@siteIds)-1)
,@str1 = substring(@siteIds,charindex('  ',@siteIds)+1,len(@siteIds))
,@state = substring(@str1,1,charindex('  ',@str1)-1)
,@str2 = substring(@str1,charindex('  ',@str1)+1,len(@str1))
,@zip = substring(@str2,1,len(@str2))

answered Jun 19, 2017 at 8:53

Coder1991

7351 gold badge5 silver badges9 bronze badges

4 Comments

Glory Raj Over a year ago

getting error at this line 'Invalid length parameter passed to the LEFT or SUBSTRING function.' at this line substring(@siteIds,1,charindex(' ',@siteIds)-1)

Glory Raj Over a year ago

two spaces between each strings

Coder1991 Over a year ago

It will start fetching from the first character till the city name + single space. No clue about the error in that substring function.

Coder1991 Over a year ago

Have you tried executing only the first substring which will fetch the city?

Panagiotis Kanavos · Accepted Answer · 2017-06-19 09:34:07Z

First, the standard answer - don't do this on the server. It's far easier to parse strings on the client.

Second, this isn't as trivial as it appears - this is parsing, not splitting a string. In this case, each token has a different meaning. All string splitting techniques, and the STRING_SPLIT command return a table of unordered tokens.

Third, the splitting technique is one of the slowest, and can't be modified parse instead of split. The fastest techniques are SQLCLR and XML parsing. Both can be modified to return a table with multiple columns.

For example, the following XML parsing function:

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO

works by surrounding each token with i tags, producing OMAHANE68117. Then it selects the contents of each I element with tags with CROSS APPLY x.nodes('i')

You can modify it to nest the elements and return the 1st, 2nd and 3rd element, as follows:

CREATE FUNCTION dbo.SplitTriplets_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT    
        Item1 = o.i.value('@value', 'nvarchar(4000)'),
        Item2 = p.i.value('@value', 'nvarchar(4000)'),
        Item3 = q.i.value('@value', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i value="' 
          + REPLACE(@List, @Delimiter, '"><i value="') 
          + '"></i></i></i>').query('.')
      ) AS a 
              OUTER APPLY x.nodes('/i') AS o(i)
              OUTER APPLY x.nodes('/i/i') AS p(i)
              OUTER APPLY x.nodes('/i/i/i') AS q(i)
  )

In this case, the generated XML is  and the OUTER APPLY x.nodes('/i') AS p(i) statements select the root and nested tags as separate tables.

This example :

declare @table table (id int,value nvarchar(200))
insert into @table
values
(1,'OMAHA  NE  68117'),
(2,'sdfA  Nw  68227')

select id,value,item1 as City,item2 as State,item3 as Zip
from @table x cross apply dbo.SplitTriplets_XML(value,'  ')

Returns :

id          value             City   State  Zip   
----------- ----------------- ------ ------ ------
1           OMAHA  NE  68117  OMAHA  NE     68117
2           sdfA  Nw  68227   sdfA   Nw     68227

Collectives™ on Stack Overflow

split the string and store it in three different variables in sql server

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related