Need to convert a SQL field based on delimiter

• mgopinath88

  Newbie

  Points: 8

  More actions

  April 9, 2015 at 2:12 pm

  #148647

  Hi All,

  Need a urgent help from you. Please find the below sample script

  Create Table Test(

  ObjectID Nvarchar(MAX),

  Owners Nvarchar(MAX),

  Authors Nvarchar(MAX))

  Insert Into Test Values

  ('0902007c80020b6f','11535194|11535195|11526658|10562876', NULL),

  ('0902007c8001f3a3','10652763|10705093','10609569|10612730'),

  ('0902007c8001d627', '10652763|10651500|10042851|10001618|10665273', '11535194|11535195|11526658|10562876')

  Select ObjectID, Owners, Authors From Test

  In my project, my requirement was to split the owners and authors field values like (11535194|11535195|11526658|10562876) into single values using delimiter and find the name of all these ID’s by joining with other table.

  We have unique id ObjectID for all these specific fields, I have to see the above results as below,

  Example given below for the 3rd row

  ObjectID OwnersAuthors OwnerName AuthorName

  0902007c8001d6271065276311535194 David Sam

  0902007c8001d6271065150011535195 James Andrew

  0902007c8001d6271004285111526658 Peter Salman

  0902007c8001d6271000161810562876 Daniel Arun

  0902007c8001d62710665273 Vicky

  Later after getting above desired result, i need to again merge the names as ID's like above.

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  April 9, 2015 at 2:33 pm

  #1790007

  I don't fully understand what you are trying to do but the splitter referenced in my signature line is the best way to split a string. This should get you started:

  Select ObjectID, Item AS author, Authors, Owners

  From Test

  CROSS APPLY dbo.DelimitedSplit8K(authors,'|')

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001