0

I have been assigned with the task of getting part numbers out of a string in our database. I know that the part will be between the first and second comma in the string but I have no idea how to go about doing this. Any one have any suggestions? This is an example of the string I will be working with LEL,SPEC2078,14 CUT LEADS,#18 1/32

Turtleman10
  • 119
  • 1
  • 2
  • 14
  • And what would be the result you want from that string? – Lamak Feb 06 '14 at 15:21
  • 2
    did you even try to google this first? "split string sql" – Scott Selby Feb 06 '14 at 15:23
  • 1
    @ScottSelby +1 and: What have you tried so far? – NickyvV Feb 06 '14 at 15:27
  • 1
    perhaps you want to run a script to seperate the whole column of comma separated values and put them in necessary columns ?? – Scott Selby Feb 06 '14 at 15:31
  • I think you'll find my answer here useful: https://stackoverflow.com/questions/21456821/select-string-till-first-or-second-space-in-string/21457036#21457036 Obviously you're looking at commas and not spaces but the same logic can be applied ;) – gvee Feb 06 '14 at 16:01

4 Answers4

1

No problem! Might be best to write a function if you have to do this for several different items inside a CSV string. 

SELECT 'LEL,SPEC2078,14 CUT LEADS,#18 1/32'

SELECT SUBSTRING('LEL,SPEC2078,14 CUT LEADS,#18 1/32',1,CHARINDEX(',','LEL,SPEC2078,14 CUT LEADS,#18 1/32',1)-1) -- Get's the first value
SELECT SUBSTRING('LEL,SPEC2078,14 CUT LEADS,#18 1/32',CHARINDEX(',','LEL,SPEC2078,14 CUT LEADS,#18 1/32',2) + 1, CHARINDEX(',','LEL,SPEC2078,14 CUT LEADS,#18 1/32',2) + CHARINDEX(',','LEL,SPEC2078,14 CUT LEADS,#18 1/32',1)) -- Get's the next value
Nick H.
  • 1,616
  • 14
  • 20
0

You will want to use Substring to pull out certain strings and CharIndex to find the position of the commas.

TTeeple
  • 2,913
  • 1
  • 13
  • 22
0

assuming the partno is in a column called p and the name of your table is products:

select SUBSTRING(p, charindex(',',p,1)+1, case when charindex(',',p,charindex(',',p,1)+1) = 0 then len(p)-charindex(',',p,1) else charindex(',',p,charindex(',',p,1)+1) end)
from products
Brett Schneider
  • 3,993
  • 2
  • 16
  • 33
  • Thank you very much I've never done anything like this in SQL so I wasn't even sure how to really ask the question. This will get me started. – Turtleman10 Feb 07 '14 at 15:25
0

Thanks for all your help this was my final solution.

select inm.fpartno, SUBSTRING(inm.fdescript, charindex(',',inm.fdescript,1)+1,

case when charindex(',',inm.fdescript,charindex(',',inm.fdescript,1)+1) = 0 then len(inm.fdescript)-charindex(',',inm.fdescript,1) else CharIndex(',', inm.fdescript, CharIndex(',', inm.fdescript) + 1) - CharIndex(',', inm.fdescript) - 1 end) from Database.dbo.inmast as inm`

Turtleman10
  • 119
  • 1
  • 2
  • 14