First / last item of a string in a nvarchar field

• timocordes 14433

  SSC Veteran

  Points: 269

  More actions

  August 29, 2017 at 6:27 am

  #323320

  Hi together,

   I actually have a little Problem and no idea to fix that. I searched @ Google but with the search terms that i thought, i didn't found a solution for that.
  A new API stores a list of items in a single SQL field like that:
  New York char(9) Washington char(9) Los Angeles Char(9) and so on. The total Citys and the Citys himself differs from field to field.
  I look for a possibility to select the first and the last item of those lists. 

  Regards
  Timo

• Ant-Green

  SSC Guru

  Points: 113445

  More actions

  August 29, 2017 at 6:37 am

  #1957215

  Sounds like you need a string splitter, which splits on TAB, from which you can select the first and last value.

  Take a look at the DelimitedSplit8k / DelimitedSplit4k functions written by Jeff in my signature block.  Once you have split the string the rest should be easy to figure out.

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  August 29, 2017 at 6:38 am

  #1957217

  You're looking for the functionality provided in Jeff Moden's string splitter named DelimitedSplit8K, and if you're data in NVARCHAR as opposed to VARCHAR, you'll want to get the NVARCHAR version of it.   Here's how you would code it using that function:
  WITH YourTable AS (

      SELECT 1 AS ID, 'New York    Washington    Los Angeles    Chicago' AS CityList
    UNION ALL
    SELECT 2, 'Chicago    Los Angeles    San Francisco    Dallas'
)
SELECT YT.ID, S.ItemNumber, S.Item
FROM YourTable AS YT
    CROSS APPLY dbo.DelimitedSplit8k(YT.CityList, CHAR(9)) AS S
ORDER BY YT.ID, S.ItemNumber;

  You can find that function in the Articles on this site by searching for "Tally Oh" after clicking on the link on the left hand side of the page for Articles.

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

• Jason A. Long

  SSC-Insane

  Points: 23780

  More actions

  August 31, 2017 at 10:54 am

  #1957700

  If you're only interested in getting the first & last values from the string, there should be no need to use a splitter function.
  See if the following returns the desired results...
  -- Steve's test data ...
WITH YourTable AS (
  SELECT 1 AS ID, 'New York    Washington    Los Angeles    Chicago' AS CityList
  UNION ALL
  SELECT 2, 'Chicago    Los Angeles    San Francisco    Dallas'
    )

  SELECT
    yt.ID,
    FirstPosition = LEFT(yt.CityList, ISNULL(NULLIF(s.fSplit, 0) - 1, 8000)),
    LastPosition = RIGHT(yt.CityList, ISNULL(NULLIF(s.rSplit, 0) - 1, 8000))
FROM
    YourTable yt
    CROSS APPLY ( VALUES (REVERSE(yt.CityList)) ) rcl (RevCityList)
    CROSS APPLY ( VALUES (CHARINDEX(CHAR(9), yt.CityList, 1), CHARINDEX(CHAR(9), rcl.RevCityList, 1)) ) s (fSplit, rSplit);

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  August 31, 2017 at 12:25 pm

  #1957714

  Similar to Jason's solution. The CTE is only to create sample data.
  
WITH SampleData AS(
  SELECT 'New York' + char(9) +'Washington' + char(9) +'Los Angeles' + char(9) +'Atlanta' As cities
  UNION ALL
  SELECT 'New York' + char(9) +'Washington' + char(9) +'Los Angeles'
  UNION ALL
  SELECT 'New York' + char(9) +'Washington'
  UNION ALL
  SELECT 'New York'
)
SELECT cities,
  LEFT( cities, CHARINDEX(CHAR(9), cities + CHAR(9)) - 1),
  RIGHT( cities, CHARINDEX(CHAR(9), REVERSE(cities) + CHAR(9)) - 1)
FROM SampleData


  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

• Avi1

  SSCrazy

  Points: 2323

  More actions

  August 31, 2017 at 12:34 pm

  #1957716

  There are multiple options, based on your requirement and can choose

  
---to get all cities, using SQL's inbulid split function
WITH YourTable AS (

    SELECT 1 AS ID, 'New York    Washington    Los Angeles    Chicago' AS CityList
  UNION ALL
  SELECT 2, 'Chicago    Los Angeles    San Francisco    Dallas'
)
SELECT YT.ID, S.Value City, ROW_NUMBER() OVER (partition by YT.id order by (select NULL))
FROM YourTable AS YT
  CROSS APPLY string_split(YT.CityList, CHAR(9)) AS S

  go
---Get only first and last city, using SQL's inbulid split function
WITH YourTable AS (

    SELECT 1 AS ID, 'New York    Washington    Los Angeles    Chicago' AS CityList
  UNION ALL
  SELECT 2, 'Chicago    Los Angeles    San Francisco    Dallas'
)
SELECT *
FROM YourTable AS YT
  CROSS APPLY
    (select top 1 FIRST_VALUE(value) OVER (partition by YT.id order by (select NULL)) FirstCity
            ,LAST_VALUE(value) OVER (partition by YT.id order by (select NULL)) LastCity
    from string_split(YT.CityList, CHAR(9)) t
) AS S
    
go
---Get only first and last city, using string functions
WITH YourTable AS (
  SELECT 1 AS ID, 'New York    Washington    Los Angeles    Chicago' AS CityList
  UNION ALL
  SELECT 2, 'Chicago    Los Angeles    San Francisco    Dallas'
)
SELECT YT.ID, YT.citylist, LEFT(citylist, CHARINDEX(char(9),citylist) ) FirstCity, right(citylist, CHARINDEX(char(9),reverse(citylist)) ) LastCity
FROM YourTable AS YT


• Jeff Moden

  SSC Guru

  Points: 1003872

  More actions

  August 31, 2017 at 3:16 pm

  #1957754

  Avi1 - Thursday, August 31, 2017 12:34 PM

  There are multiple options, based on your requirement and can choose

  
---to get all cities, using SQL's inbulid split function
WITH YourTable AS (

    SELECT 1 AS ID, 'New York    Washington    Los Angeles    Chicago' AS CityList
  UNION ALL
  SELECT 2, 'Chicago    Los Angeles    San Francisco    Dallas'
)
SELECT YT.ID, S.Value City, ROW_NUMBER() OVER (partition by YT.id order by (select NULL))
FROM YourTable AS YT
  CROSS APPLY string_split(YT.CityList, CHAR(9)) AS S

  go
---Get only first and last city, using SQL's inbulid split function
WITH YourTable AS (

    SELECT 1 AS ID, 'New York    Washington    Los Angeles    Chicago' AS CityList
  UNION ALL
  SELECT 2, 'Chicago    Los Angeles    San Francisco    Dallas'
)
SELECT *
FROM YourTable AS YT
  CROSS APPLY
    (select top 1 FIRST_VALUE(value) OVER (partition by YT.id order by (select NULL)) FirstCity
            ,LAST_VALUE(value) OVER (partition by YT.id order by (select NULL)) LastCity
    from string_split(YT.CityList, CHAR(9)) t
) AS S
    
go
---Get only first and last city, using string functions
WITH YourTable AS (
  SELECT 1 AS ID, 'New York    Washington    Los Angeles    Chicago' AS CityList
  UNION ALL
  SELECT 2, 'Chicago    Los Angeles    San Francisco    Dallas'
)
SELECT YT.ID, YT.citylist, LEFT(citylist, CHARINDEX(char(9),citylist) ) FirstCity, right(citylist, CHARINDEX(char(9),reverse(citylist)) ) LastCity
FROM YourTable AS YT


  While the use of the relatively new STRING_SPLIT function is a good lure, MS does not guarantee the order of return.  I agree that it's likely that the return order will be the correct order but, without a written guarantee, you're taking a chance.  Notice also than none of the examples provided by MS for the function have any dependency whatsoever on the order or the returned elements.
  https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Avi1

  SSCrazy

  Points: 2323

  More actions

  August 31, 2017 at 3:46 pm

  #1957759

  Jeff Moden - Thursday, August 31, 2017 3:16 PM

  Avi1 - Thursday, August 31, 2017 12:34 PM

  There are multiple options, based on your requirement and can choose

  
---to get all cities, using SQL's inbulid split function
WITH YourTable AS (

    SELECT 1 AS ID, 'New York    Washington    Los Angeles    Chicago' AS CityList
  UNION ALL
  SELECT 2, 'Chicago    Los Angeles    San Francisco    Dallas'
)
SELECT YT.ID, S.Value City, ROW_NUMBER() OVER (partition by YT.id order by (select NULL))
FROM YourTable AS YT
  CROSS APPLY string_split(YT.CityList, CHAR(9)) AS S

  go
---Get only first and last city, using SQL's inbulid split function
WITH YourTable AS (

    SELECT 1 AS ID, 'New York    Washington    Los Angeles    Chicago' AS CityList
  UNION ALL
  SELECT 2, 'Chicago    Los Angeles    San Francisco    Dallas'
)
SELECT *
FROM YourTable AS YT
  CROSS APPLY
    (select top 1 FIRST_VALUE(value) OVER (partition by YT.id order by (select NULL)) FirstCity
            ,LAST_VALUE(value) OVER (partition by YT.id order by (select NULL)) LastCity
    from string_split(YT.CityList, CHAR(9)) t
) AS S
    
go
---Get only first and last city, using string functions
WITH YourTable AS (
  SELECT 1 AS ID, 'New York    Washington    Los Angeles    Chicago' AS CityList
  UNION ALL
  SELECT 2, 'Chicago    Los Angeles    San Francisco    Dallas'
)
SELECT YT.ID, YT.citylist, LEFT(citylist, CHARINDEX(char(9),citylist) ) FirstCity, right(citylist, CHARINDEX(char(9),reverse(citylist)) ) LastCity
FROM YourTable AS YT


  While the use of the relatively new STRING_SPLIT function is a good lure, MS does not guarantee the order of return.  I agree that it's likely that the return order will be the correct order but, without a written guarantee, you're taking a chance.  Notice also than none of the examples provided by MS for the function have any dependency whatsoever on the order or the returned elements.
  https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

  Agree. Thanks for input

• Mike Good

  SSCertifiable

  Points: 7415

  More actions

  September 1, 2017 at 5:52 am

  #1957804

  this one gets my vote

• Mike Good

  SSCertifiable

  Points: 7415

  More actions

  September 1, 2017 at 5:53 am

  #1957805

  Doh.  That would be the Luis Cazares solution.

• Jeff Moden

  SSC Guru

  Points: 1003872

  More actions

  September 1, 2017 at 6:38 am

  #1957814

  Mike Good - Friday, September 1, 2017 5:53 AM

  Doh.  That would be the Luis Cazares solution.

  Agreed.  It's simple and very likely to be the fastest especially since he's one of the folks that knows to avoid a double REVERSE when 1 will do.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)