String division

Question

HI,

I have following strings.

-abv-derff-fg-k-(separated by -)

I need abv derff fg K all 4 separately in diff variables.

Suggest please.

---

Porus

Answer 1

Try:

declare @String varchar(100)

set @String = '-abv-derff-fg-k-'

  SELECT x.i.value('p[1]', 'varchar(100)') as [First],
 x.i.value('p[2]', 'varchar(100)') as [Seconds],
 x.i.value('p[3]', 'varchar(100)') as [Third],
 x.i.value('p[4]', 'varchar(100)') as [Fourth]
 
 FROM (SELECT [XML] = CONVERT(XML, '<i><p>' 
                    + REPLACE(substring(@String,2, len(@String)-1), '-', '</p><p>') 
                    + '</p></i>').query('.')) a
cross apply [XML].nodes('i') as x(i)                            

It's easy to declare 4 variables and instead of new column names use variable names in the above code.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Thanks Naomi. atcually I need to use these in SSIS package derived column. So wanted single expression for each computed variable.

---

Porus

Answer 3

Clarify please?  Does that mean you want 4 separate statements, one for each word?  Just get 4 variables, such as @Word1, @Word2, @Word3, and @Word4, by any means, and it will always be just 4 variables, the string won't grow or be variable in size?  Or would having them in a results set be better for you?

This may not be the specific answer you need, but this takes the resultset approach, returning a row for each split out word in the input string.

Declare @Numbers Table (Num int Primary Key)
Insert @Numbers Select top 100 Row_Number() over(order by @@ServerName) from SYS.MESSAGES

Declare @String Varchar(99)
Set @String = '-abv-derff-fg-k-'

Declare @StringTable Table (SID int identity, TString varchar(99))
Insert @StringTable 
  Select '-abv-derff-fg-k-' UNION ALL
  Select '-def-ghijk-lm-n-' UNION ALL
  Select '-opq-rstuv-wx-y-'

METHOD1_Single_String:
		Select *, SubString(String, Num+1, NextDash - Num - 1) as ThisWord, Row_Number() over(Order by @@ServerName) as WordNum
		  From @Numbers N
		Inner Join (Select @String as String) S on SubString(String, Num, 1) = '-'
		Cross Apply (Select CharIndex('-', @String, Num+1) as NextDash) as CA1
		Where Num < Len(String)

METHOD1_From_Table:
		Select *, SubString(TString, Num+1, NextDash - Num - 1) as ThisWord, Row_Number() over(Partition by TString order by Num) as WordNum
		  From @Numbers N
		Inner Join @STRINGTABLE S on SubString(TString, Num, 1) = '-'
		Cross Apply (Select CharIndex('-', TString, Num+1) as NextDash) as CA1
		Where Num < Len(TString)

Output of the second query, col 5 = each word and col6= the word number

Num	SID	TString	         NextDash	ThisWord	WordNum
1	1	-abv-derff-fg-k-	5	abv	1
5	1	-abv-derff-fg-k-	11	derff	2
11	1	-abv-derff-fg-k-	14	fg	3
14	1	-abv-derff-fg-k-	16	k	4
1	2	-def-ghijk-lm-n-	5	def	1
5	2	-def-ghijk-lm-n-	11	ghijk	2
11	2	-def-ghijk-lm-n-	14	lm	3
14	2	-def-ghijk-lm-n-	16	n	4
1	3	-opq-rstuv-wx-y-	5	opq	1
5	3	-opq-rstuv-wx-y-	11	rstuv	2
11	3	-opq-rstuv-wx-y-	14	wx	3
14	3	-opq-rstuv-wx-y-	16	y	4

Answer 4

The operation is commonly called splitting a delimited string:

http://www.sqlusa.com/bestpractices/training/scripts/userdefinedfunction/

The usual output is a @tablevariable.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012