# String division

### 問題

• HI,

I have following strings.

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

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

Porus

2012年5月1日 下午 09:57

### 解答

• 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```

• 已編輯 2012年5月2日 上午 11:24 replaced output formatting
• 已標示為解答 2012年5月14日 上午 07:15
2012年5月2日 上午 11:22

### 所有回覆

• 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

2012年5月1日 下午 10:34
• Thanks Naomi. atcually I need to use these in SSIS package derived column. So wanted single expression for each computed variable.

Porus

2012年5月2日 上午 06:46
• 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```

• 已編輯 2012年5月2日 上午 11:24 replaced output formatting
• 已標示為解答 2012年5月14日 上午 07:15
2012年5月2日 上午 11:22
• The operation is commonly called splitting a delimited string:

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

The usual output is a @tablevariable.

2012年5月8日 下午 03:40