String division

모든 응답

• 2012년 5월 1일 화요일 오후 10:34

  중재자

   

   
  

  0

  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월 2일 수요일 오전 6:46

   

   
  

  0
  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일 수요일 오전 11:22

   

   
  

  0

  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

  
  
  

  
  

  
  

  
  

  
  

  
  

  • 편집됨 johnqflorida 2012년 5월 2일 수요일 오전 11:24 replaced output formatting
  • 답변으로 표시됨 Iric WenModerator 2012년 5월 14일 월요일 오전 7:15
  •  
  • 응답
  • 인용

   
• 2012년 5월 8일 화요일 오후 3:40

  중재자

   

   
  

  0

  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

  
  

  • 편집됨 Kalman TothMicrosoft Community Contributor, Moderator 2012년 10월 7일 일요일 오후 9:35
  •  
  • 응답
  • 인용