Split string

• sql_2005_fan

  SSCommitted

  Points: 1871

  More actions

  September 14, 2017 at 10:47 am

  #402485

  Hi,

  I have string with streetname ,city and state in the following folrmat.

  I need to split this into three diff values.
  Wenday Ln [frisco, tx)
  Street=Wenday Ln
  City=Frisco
  State=tx
  How can I do this with the two different delimeters.

  Thanks.

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  September 14, 2017 at 10:53 am

  #1959466

  Please post consumable sample data, create table statement with insert statements. Also check out the new String_Split() function in SQL 2016.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 14, 2017 at 11:13 am

  #1959470

  sql_2005_fan - Thursday, September 14, 2017 10:47 AM

  Hi,

  I have string with streetname ,city and state in the following folrmat.

  I need to split this into three diff values.
  Wenday Ln [frisco, tx)
  Street=Wenday Ln
  City=Frisco
  State=tx
  How can I do this with the two different delimeters.

  Thanks.

  Is that a left square bracket and a right round bracket? The code is relatively simple using charindex and substring functions.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  September 14, 2017 at 11:43 am

  #1959478

  sql_2005_fan - Thursday, September 14, 2017 10:47 AM

  Hi,

  I have string with streetname ,city and state in the following folrmat.

  I need to split this into three diff values.
  Wenday Ln [frisco, tx)
  Street=Wenday Ln
  City=Frisco
  State=tx
  How can I do this with the two different delimeters.

  Thanks.

  This is still relatively easy, using a string splitter function.   Jeff Moden has one posted on this site under the Articles section, where you can search for "Tally Oh", and you'll find it.  The name of that function is DelimitedSplit8K.   Here's some sample code:
  CREATE TABLE #YourTable (
    ID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    YourFieldName varchar(25)
);
INSERT INTO #YourTable (YourFieldName)
SELECT 'Wenday Ln [frisco, tx)'

  SELECT YT.ID,
    MAX(CASE S1.ItemNumber WHEN 1 THEN S1.Item ELSE NULL END) AS Street,
    MAX(S2.Item) AS City,
    MAX(REPLACE(S3.Item , ')', '')) AS [State]
FROM #YourTable AS YT
  CROSS APPLY dbo.DelimitedSplit8K(YT.YourFieldName, '[') AS S1
  OUTER APPLY (SELECT S.Item FROM dbo.DelimitedSplit8K(S1.Item, ',') AS S WHERE S1.ItemNumber = 2 AND S.ItemNumber = 1) AS S2
  OUTER APPLY (SELECT S.Item FROM dbo.DelimitedSplit8K(S1.Item, ',') AS S WHERE S1.ItemNumber = 2 AND S.ItemNumber = 2) AS S3
GROUP BY YT.ID;

  DROP TABLE #YourTable;

  You can find the code for the splitter at the bottom of the article in a zip file, here:  http://www.sqlservercentral.com/articles/Tally+Table/72993/

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 14, 2017 at 12:08 pm

  #1959480

  For this, I might keep a more straightforward approach.
  
SELECT LEFT( YourFieldName, CHARINDEX('[', YourFieldName + '[')-1),
   SUBSTRING( YourFieldName, CHARINDEX('[', YourFieldName + '[')+1, CHARINDEX(',', YourFieldName + ',', CHARINDEX('[', YourFieldName + '[')) - CHARINDEX('[', YourFieldName + '[')-1),
   SUBSTRING( YourFieldName, CHARINDEX(',', YourFieldName + ',', CHARINDEX('[', YourFieldName + '[')) + 2, 2)
FROM #YourTable;


  EDIT:
  If you want an improved readability:
  
SELECT LEFT( YourFieldName, Street.Delimeter-1),
     SUBSTRING( YourFieldName, Street.Delimeter + 1, City.Delimeter - Street.Delimeter - 1),
      SUBSTRING( YourFieldName, City.Delimeter + 2, 2)
FROM #YourTable
CROSS APPLY (SELECT CHARINDEX('[', YourFieldName + '[')) Street(Delimeter)
CROSS APPLY (SELECT CHARINDEX(',', YourFieldName + ',', Street.Delimeter)) City(Delimeter);


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2