How to Split the Sting in SQl Server

• kbhanu15

  SSCarpal Tunnel

  Points: 4498

  More actions

  July 20, 2018 at 3:17 am

  #354821

  Hi Team,

  I have a input string like below.
  declare @country varchar(max) = 'china or india and us not canda or swiss '
  select @country
  
  My expected output like below:
  ' ((china and us )or(india or swiss))not canada'

  please help me to get this output.

  Thanks
  Bhhanu

• Thom A

  SSC Guru

  Points: 99129

  More actions

  July 20, 2018 at 3:47 am

  #1998350

  That's not really string splitting. Splitting would be something like taking the string "a,b,c,d" and expecting the output:
  a
  b
  c
  d

  What you want to do here is string manipulation, but, even more importantly, you're basing that manipulation on grammar ("and"/"or") and I can't even work out why "china" and "us" are grouped, as are "india" and "swiss". Whatever your logic is here SQL Server is far from the right too to try and create a solution; string manipulation is not it's forté (quite the opposite). It would be like using a large hammer to try and create a finely crafted soft wood statuette; it's completely unsuited for the task at hard.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• debasis.yours

  Hall of Fame

  Points: 3457

  More actions

  July 20, 2018 at 4:52 am

  #1998355

  Hi Bhanu,

  This is somewhat built-up (I am sure it needs refactoring), but you will be better doing that:
  

  declare @country varchar(max) = 'china or india and us not canda or swiss '
declare @andpart TABLE(AndPart varchar(max))
declare @orpart TABLE(OrPart varchar(max))
declare @notpart TABLE(NotPart varchar(max))

  declare @manipulate varchar(max) = replace(replace(replace(@country, ' or ','|'),' and ','&'), ' not ', '!')
DECLARE @PartTable Table(Parts VARCHAR(MAX))

  INSERT @PartTable
SELECT * FROM string_split(@manipulate,'&')

  -- Separate And parts
INSERT @andpart
SELECT CASE
            WHEN CHARINDEX('|',Parts,1)>0 THEN substring(Parts,1, CHARINDEX('|',Parts,1)-1)
            WHEN CHARINDEX('!',Parts,1)>0 THEN substring(Parts,1, CHARINDEX('!',Parts,1)-1)
     END AS Parts
FROM @PartTable

  UPDATE @andpart SET AndPart = SUBSTRING(AndPart,1, CHARINDEX('|',AndPart)-1) WHERE CHARINDEX('|', AndPart)>0
UPDATE @andpart SET AndPart = SUBSTRING(AndPart,1, CHARINDEX('!',AndPart)-1) WHERE CHARINDEX('!', AndPart)>0

  DELETE @PartTable

  INSERT @PartTable
SELECT * FROM string_split(@manipulate,'|')

  -- Separate Or parts
INSERT @orpart
SELECT CASE
            WHEN CHARINDEX('&',Parts,1)>0 THEN substring(Parts,1, CHARINDEX('&',Parts,1)-1)
            WHEN CHARINDEX('!',Parts,1)>0 THEN substring(Parts,1, CHARINDEX('!',Parts,1)-1)
            ELSE Parts
     END AS Parts
FROM @PartTable

  UPDATE @orpart SET OrPart = SUBSTRING(OrPart,1, CHARINDEX('|',OrPart)-1) WHERE CHARINDEX('|', OrPart)>0
UPDATE @orpart SET OrPart = SUBSTRING(OrPart,1, CHARINDEX('&',OrPart)-1) WHERE CHARINDEX('&', OrPart)>0
DELETE FROM @orpart WHERE OrPart IN(SELECT AndPart FROM @andpart)

  DELETE @PartTable

  INSERT @PartTable
SELECT * FROM string_split(@manipulate,'!')

  -- Separate Not parts
INSERT @notpart
SELECT CASE
            WHEN CHARINDEX('&',Parts,1)>0 THEN substring(Parts,1, CHARINDEX('&',Parts,1)-1)
            WHEN CHARINDEX('|',Parts,1)>0 THEN substring(Parts,1, CHARINDEX('|',Parts,1)-1)
            ELSE Parts
     END AS Parts
FROM @PartTable

  UPDATE @notpart SET NotPart = SUBSTRING(NotPart,1, CHARINDEX('|',NotPart)-1) WHERE CHARINDEX('|', NotPart)>0
UPDATE @notpart SET NotPart = SUBSTRING(NotPart,1, CHARINDEX('&',NotPart)-1) WHERE CHARINDEX('&', NotPart)>0

  DELETE FROM @notpart WHERE NotPart IN(SELECT AndPart FROM @andpart UNION ALL SELECT OrPart FROM @orpart)

  DECLARE @AndStuff VARCHAR(MAX)
DECLARE @OrStuff VARCHAR(MAX)
DECLARE @NotStuff VARCHAR(MAX)
SELECT @AndStuff = STUFF((SELECT ' and ' + AndPart FROM @andpart FOR XML PATH('')), 1, 1, ' ')
SELECT @AndStuff = SUBSTRING(@AndStuff, 6, LEN(@AndStuff))
SELECT @OrStuff = STUFF((SELECT ' or ' + OrPart FROM @orpart FOR XML PATH('')), 1, 1, ' ')
SELECT @OrStuff = SUBSTRING(@OrStuff, 5, LEN(@OrStuff))
SELECT @NotStuff = STUFF((SELECT ' not ' + notPart FROM @notpart FOR XML PATH('')), 1, 1, ' ')
SELECT @NotStuff = SUBSTRING(@NotStuff, 5, LEN(@NotStuff))

  SELECT '((' + @AndStuff + ') or (' + @OrStuff + '))' + CASE WHEN LEN(TRIM(@NotStuff))>0 THEN ' not ' + @NotStuff ELSE '' END


• kbhanu15

  SSCarpal Tunnel

  Points: 4498

  More actions

  July 20, 2018 at 5:18 am

  #1998359

  Hi,

  Looks like it will work but i am using SQL Server 2014.

  we dont have this function "string_split"

  can you please provide alternative to this.

  thanks for your time and support.

  Thanks
  Bhanu

• Thom A

  SSC Guru

  Points: 99129

  More actions

  July 20, 2018 at 6:35 am

  #1998372

  kbhanu15 - Friday, July 20, 2018 5:18 AM

  Hi,

  Looks like it will work but i am using SQL Server 2014.

  we dont have this function "string_split"

  can you please provide alternative to this.

  thanks for your time and support.

  Thanks
  Bhanu

  Dare I ask, but why did you post in the 2016 forum then? 🙂

  Search delimitedsplit8k.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 20, 2018 at 7:56 am

  #1998395

  Thom A - Friday, July 20, 2018 6:35 AM

  kbhanu15 - Friday, July 20, 2018 5:18 AM

  Hi,

  Looks like it will work but i am using SQL Server 2014.

  we dont have this function "string_split"

  can you please provide alternative to this.

  thanks for your time and support.

  Thanks
  Bhanu

  Dare I ask, but why did you post in the 2016 forum then? 🙂

  Search delimitedsplit8k.

  Which is designed to work strings defined up to VARCHAR(8000).  Modifying the function to work with VARCHAR(MAX) will kill its performance.

• Thom A

  SSC Guru

  Points: 99129

  More actions

  July 20, 2018 at 8:02 am

  #1998400

  Lynn Pettis - Friday, July 20, 2018 7:56 AM

  Thom A - Friday, July 20, 2018 6:35 AM

  Dare I ask, but why did you post in the 2016 forum then? 🙂

  Search delimitedsplit8k.

  Which is designed to work strings defined up to VARCHAR(8000).  Modifying the function to work with VARCHAR(MAX) will kill its performance.

  Very true, however, if you're trying to split a string longer than 8000 character in SQL Server, I suspect that the problem is much more data inherent. 🙂

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 20, 2018 at 8:05 am

  #1998402

  kbhanu15 - Friday, July 20, 2018 3:17 AM

  Hi Team,

  I have a input string like below.
  declare @country varchar(max) = 'china or india and us not canda or swiss '
  select @country
  
  My expected output like below:
  ' ((china and us )or(india or swiss))not canada'

  please help me to get this output.

  Thanks
  Bhhanu

  There is also a missing AND or OR between US and NOT CANADA.  Please, provide an in depth explanation of the logic you are trying to implement.  But I also agree, T-SQL isn't where you really want to implement this logic.

• Jeff Moden

  SSC Guru

  Points: 1004318

  More actions

  July 20, 2018 at 8:59 am

  #1998426

  Thom A - Friday, July 20, 2018 8:02 AM

  Lynn Pettis - Friday, July 20, 2018 7:56 AM

  Thom A - Friday, July 20, 2018 6:35 AM

  Dare I ask, but why did you post in the 2016 forum then? 🙂

  Search delimitedsplit8k.

  Which is designed to work strings defined up to VARCHAR(8000).  Modifying the function to work with VARCHAR(MAX) will kill its performance.

  Very true, however, if you're trying to split a string longer than 8000 character in SQL Server, I suspect that the problem is much more data inherent. 🙂

  Heh... PREACH IT BROTHER!!!! 😀

  --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)