??1?---去掉字符串分隔符
??2?
??3?CREATE?TABLE?inventory?(fullname?varchar(60)?NOT?NULL)
??4?go
??5?INSERT?inventory(fullname)
??6?VALUES?('2004?Inventory:Ex.Plant?Farm1:1st?Cut:Premium:0094')
??7?INSERT?inventory(fullname)
??8?VALUES?('2004?Inventory')
??9?INSERT?inventory(fullname)
?10?VALUES?('2004?Inventory:Ex.Plant?Farm1:1st?Cut')
?11?go
?12?
?13?SELECT?inventory,?plant?=?nullif(plant,?''),?cut?=?nullif(cut,?''),
?14?grade?=?nullif(grade,?''),?lot#?=?nullif(lot#,?'')
?15?FROM
?16?(SELECT?inventory,?plant,?cut,
?17?left(rest,?charindex(':',?rest?+?':')?-?1)?AS?grade,
?18?substring(rest,?charindex(':',?rest?+?':')?+?1,
?19?len(rest))?AS?lot#
?20?FROM
?21?(SELECT?inventory,?plant,
?22?left(rest,?charindex(':',?rest?+?':')?-?1)?AS?cut,
?23?substring(rest,?charindex(':',?rest?+?':')?+?1,
?24?len(rest))?AS?rest
?25?FROM
?26?(SELECT?inventory,
?27?left(rest,?charindex(':',?rest?+?':')?-?1)?AS?plant,
?28?substring(rest,?charindex(':',?rest?+?':')?+?1,
?29?len(rest))?AS?rest
?30?FROM
?31?(SELECT?left(fullname,?charindex(':',?fullname?+?':')?-?1)?AS?inventory,
?32?substring(fullname,?charindex(':',?fullname?+?':')?+?1,
?33?len(fullname))?AS?rest
?34?FROM?inventory)?AS?a)?AS?b)?AS?c)?AS?d
?35?go
?36?
?37?DROP?TABLE?inventory
?38?
?39?--Below?is?a?UDF?to?take?a?comma?delim?value?and?return?in?table?format,
?40?--the?split?results..?Use?like?select?*?from?dbo.Split('1,2,3,3',',')
?41?CREATE?FUNCTION?dbo.Split(@sText?varchar(8000),?@sDelim?varchar(20)?=?'')
?42?RETURNS?@retArray?TABLE?(idx?smallint?Primary?Key,?value?varchar(8000))
?43?AS
?44?BEGIN
?45?DECLARE?@idx?int,
?46?@value?varchar(8000),
?47?@bcontinue?bit,
?48?@iStrike?int,
?49?@iDelimlength?int
?50?
?51?IF?@sDelim?=?'Space'
?52?BEGIN
?53?SET?@sDelim?=?'?'
?54?END
?55?
?56?SET?@idx?=?0
?57?SET?@sText?=?LTrim(RTrim(@sText))
?58?SET?@iDelimlength?=?DATALENGTH(@sDelim)
?59?SET?@bcontinue?=?1
?60?
?61?if(Len(@sText)?=?0)
?62?return
?63?
?64?IF?NOT?((@iDelimlength?=?0)?or?(@sDelim?=?'Empty'))
?65?BEGIN
?66?WHILE?@bcontinue?=?1
?67?BEGIN
?68?
?69?--If?you?can?find?the?delimiter?in?the?text,?retrieve?the?first?element
?70?and
?71?--insert?it?with?its?index?into?the?return?table.
?72?
?73?IF?CHARINDEX(@sDelim,?@sText)>0
?74?BEGIN
?75?SET?@value?=?SUBSTRING(@sText,1,?CHARINDEX(@sDelim,@sText)-1)
?76?BEGIN
?77?INSERT?@retArray?(idx,?value)
?78?VALUES?(@idx,?@value)
?79?END
?80?
?81?--Trim?the?element?and?its?delimiter?from?the?front?of?the?string.
?82?--Increment?the?index?and?loop.
?83?SET?@iStrike?=?DATALENGTH(@value)?+?@iDelimlength
?84?SET?@idx?=?@idx?+?1
?85?SET?@sText?=?LTrim(Right(@sText,DATALENGTH(@sText)?-?@iStrike))
?86?
?87?END
?88?ELSE
?89?BEGIN
?90?--If?you?can?t?find?the?delimiter?in?the?text,?@sText?is?the?last
?91?value?in
?92?--@retArray.
?93?SET?@value?=?@sText
?94?BEGIN
?95?INSERT?@retArray?(idx,?value)
?96?VALUES?(@idx,?@value)
?97?END
?98?--Exit?the?WHILE?loop.
?99?SET?@bcontinue?=?0
100?END
101?END
102?END
103?ELSE
104?BEGIN
105?WHILE?@bcontinue=1
106?BEGIN
107?--If?the?delimiter?is?an?empty?string,?check?for?remaining?text
108?--instead?of?a?delimiter.?Insert?the?first?character?into?the
109?--retArray?table.?Trim?the?character?from?the?front?of?the?string.
110?--Increment?the?index?and?loop.
111?IF?DATALENGTH(@sText)>1
112?BEGIN
113?SET?@value?=?SUBSTRING(@sText,1,1)
114?BEGIN
115?INSERT?@retArray?(idx,?value)
116?VALUES?(@idx,?@value)
117?END
118?SET?@idx?=?@idx+1
119?SET?@sText?=?SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
120?
121?END
122?ELSE
123?BEGIN
124?--One?character?remains.
125?--Insert?the?character,?and?exit?the?WHILE?loop.
126?INSERT?@retArray?(idx,?value)
127?VALUES?(@idx,?@sText)
128?SET?@bcontinue?=?0
129?END
130?END
131?
132?END
133?
134?RETURN
135?END
136?
137?GO
138?
139?SET?QUOTED_IDENTIFIER?OFF
140?GO
141?SET?ANSI_NULLS?ON
142?GO
143?
144?
145?
146?---Here?is?an?example?usage?of?the?T-SQL?SPLIT?function?in?action
147?CREATE?FUNCTION?SPLIT
148?(
149???@s?nvarchar(max),
150???@trimPieces?bit,
151???@returnEmptyStrings?bit
152?)
153?returns?@t?table?(val?nvarchar(max))
154?as
155?begin
156?
157?declare?@i?int,?@j?int
158?select?@i?=?0,?@j?=?(len(@s)?-?len(replace(@s,',','')))
159?
160?;with?cte
161?as
162?(
163???select
164?????i?=?@i?+?1,
165?????s?=?@s,
166?????n?=?substring(@s,?0,?charindex(',',?@s)),
167?????m?=?substring(@s,?charindex(',',?@s)+1,?len(@s)?-?charindex(',',?@s))
168?
169???union?all
170?
171???select
172?????i?=?cte.i?+?1,
173?????s?=?cte.m,
174?????n?=?substring(cte.m,?0,?charindex(',',?cte.m)),
175?????m?=?substring(
176???????cte.m,
177???????charindex(',',?cte.m)?+?1,
178???????len(cte.m)-charindex(',',?cte.m)
179?????)
180???from?cte
181???where?i?<=?@j
182?)
183?insert?into?@t?(val)
184?select?pieces
185?from
186?(
187???select
188???case
189?????when?@trimPieces?=?1
190?????then?ltrim(rtrim(case?when?i?<=?@j?then?n?else?m?end))
191?????else?case?when?i?<=?@j?then?n?else?m?end
192???end?as?pieces
193???from?cte
194?)?t
195?where
196???(@returnEmptyStrings?=?0?and?len(pieces)?>?0)
197???or?(@returnEmptyStrings?=?1)
198?option?(maxrecursion?0)
199?
200?return
201?
202?end
203?
204?GO
205?--測試
206?declare?@s?nvarchar(max)
207?select?@s?=?N',,45?,?1,?56,?346?456,8,5,?,d,1,4,?5?9?,t,,4,5?,,,?w,?3,,'
208?select?*?from?dbo.split(@s,1,0)
209?
210?
211?---
212?DECLARE?@t?table(c1?varchar(200))
213?INSERT?INTO?@t?(c1)?VALUES('LongerValue/Place_1/Last_Place')
214?
215?SELECT?site?=?PARSENAME(REPLACE(c1,?'/',?'.'),?3),
216?floor?=?PARSENAME(REPLACE(c1,?'/',?'.'),?2),
217?location?=?PARSENAME(REPLACE(c1,?'/',?'.'),?1)
218?FROM?@t
219?
220?
221?CREATE?FUNCTION?dbo.Split?(@sep?char(1),?@s?varchar(512))
222?RETURNS?table
223?AS
224?RETURN?(
225?????WITH?Pieces(pn,?start,?stop)?AS?(
226???????SELECT?1,?1,?CHARINDEX(@sep,?@s)
227???????UNION?ALL
228???????SELECT?pn?+?1,?stop?+?1,?CHARINDEX(@sep,?@s,?stop?+?1)
229???????FROM?Pieces
230???????WHERE?stop?>?0
231?????)
232?????SELECT?pn,
233???????SUBSTRING(@s,?start,?CASE?WHEN?stop?>?0?THEN?stop-start?ELSE?512?END)?AS?s
234?????FROM?Pieces
235???)
236?
237?
238?--I?use?this?function?(SQL?Server?2005?and?above).
239?create?function?[dbo].[Split]
240?(
241?????@string?nvarchar(4000),
242?????@delimiter?nvarchar(10)
243?)
244?returns?@table?table
245?(
246?????[Value]?nvarchar(4000)
247?)
248?begin
249?????declare?@nextString?nvarchar(4000)
250?????declare?@pos?int,?@nextPos?int
251?????declare?@commaCheck?nvarchar(1)
252?
253?????set?@nextString?=?''
254?????set?@commaCheck?=?right(@string,?1)
255?????set?@string?=?@string?+?@delimiter
256?
257?????set?@pos?=?charindex(@delimiter,?@string)
258?????set?@nextPos?=?1
259?????while?(@pos?<>?0)
260?????begin
261?????????set?@nextString?=?substring(@string,?1,?@pos?-?1)
262?
263?????????insert?into?@table
264?????????(
265?????????????????[Value]
266?????????)
267?????????values
268?????????(
269?????????????????@nextString
270?????????)
271?
272?????????set?@string?=?substring(@string,?@pos?+?1,?len(@string))
273?????????set?@nextPos?=?@pos
274?????????set?@pos?=?charindex(@delimiter,?@string)
275?????end
276?????return
277?end
278?
279?--
280?create?FUNCTION?dbo.fn_Split2?(@sep?nvarchar(10),?@s?nvarchar(4000))
281?RETURNS?table
282?AS
283?RETURN?(
284?????WITH?Pieces(pn,?start,?stop)?AS?(
285???????SELECT?1,?1,?CHARINDEX(@sep,?@s)
286???????UNION?ALL
287???????SELECT?pn?+?1,?stop?+?(datalength(@sep)/2),?CHARINDEX(@sep,?@s,?stop?+?(datalength(@sep)/2))
288???????FROM?Pieces
289???????WHERE?stop?>?0
290?????)
291?????SELECT?pn,
292???????SUBSTRING(@s,?start,?CASE?WHEN?stop?>?0?THEN?stop-start?ELSE?4000?END)?AS?s
293?????FROM?Pieces
294???)
295?--
296?ALTER?Function?[dbo].[SplitStr]?(?
297?????????@txt?text?
298?)?
299?Returns?@tmp?Table?
300?????????(?
301?????????????????value?varchar(127)
302?????????)?
303?as?
304?BEGIN?
305?????????declare?@str?varchar(8000)?
306?????????????????,?@Beg?int?
307?????????????????,?@last?int?
308?????????????????,?@size?int?
309?
310?????????set?@size=datalength(@txt)?
311?????????set?@Beg=1?
312?
313?
314?????????set?@str=substring(@txt,@Beg,8000)?
315?????????IF?len(@str)<8000?set?@Beg=@size?
316?????????ELSE?BEGIN?
317?????????????????set?@last=charindex(',',?reverse(@str))?
318?????????????????set?@str=substring(@txt,@Beg,8000-@last)?
319?????????????????set?@Beg=@Beg+8000-@last+1?
320?????????END?
321?
322?????????declare?@workingString?varchar(25)?
323?????????????????,?@stringindex?int?
324?
325?
326?
327?????????while?@Beg<=@size?Begin?
328?????????????????WHILE?LEN(@str)?>?0?BEGIN?
329?????????????????????????SELECT?@StringIndex?=?CHARINDEX(',',?@str)?
330?
331?????????????????????????SELECT?
332?????????????????????????????????@workingString?=?CASE?
333?????????????????????????????????????????WHEN?@StringIndex?>?0?THEN?SUBSTRING(@str,?1,?@StringIndex-1)?
334?????????????????????????????????????????ELSE?@str?
335?????????????????????????????????END?
336?
337?????????????????????????INSERT?INTO?
338?????????????????????????????????@tmp(value)
339?????????????????????????VALUES?
340?????????????????????????????????(cast(rtrim(ltrim(@workingString))?as?varchar(127)))
341?????????????????????????SELECT?@str?=?CASE?
342?????????????????????????????????WHEN?CHARINDEX(',',?@str)?>?0?THEN?SUBSTRING(@str,?@StringIndex+1,?LEN(@str))?
343?????????????????????????????????ELSE?''?
344?????????????????????????END?
345?????????????????END?
346?????????????????set?@str=substring(@txt,@Beg,8000)?
347?
348?????????????????if?@Beg=@size?set?@Beg=@Beg+1?
349?????????????????else?IF?len(@str)<8000?set?@Beg=@size?
350?????????????????ELSE?BEGIN?
351?????????????????????????set?@last=charindex(',',?reverse(@str))?
352?????????????????????????set?@str=substring(@txt,@Beg,8000-@last)?
353?????????????????????????set?@Beg=@Beg+8000-@last+1?
354?
355?????????????????END?
356?????????END?????
357?
358?????????return
359?END
360?--
361?SELECT?substring(commaSeparatedTags,0,charindex(',',commaSeparatedTags))
362?
363?--
364?CREATE?FUNCTION?SplitWords(@text?varchar(8000))
365????RETURNS?@words?TABLE?(
366???????pos?smallint?primary?key,
367???????value?varchar(8000)
368????)
369?AS
370?BEGIN
371????DECLARE
372???????@pos?smallint,
373???????@i?smallint,
374???????@j?smallint,
375???????@s?varchar(8000)
376?
377????SET?@pos?=?1
378????WHILE?@pos?<=?LEN(@text)?
379????BEGIN?
380???????SET?@i?=?CHARINDEX('?',?@text,?@pos)
381???????SET?@j?=?CHARINDEX(',',?@text,?@pos)
382???????IF?@i?>?0?OR?@j?>?0
383???????BEGIN
384??????????IF?@i?=?0?OR?(@j?>?0?AND?@j?<?@i)
385?????????????SET?@i?=?@j
386?
387??????????IF?@i?>?@pos
388??????????BEGIN
389?????????????--?@i?now?holds?the?earliest?delimiter?in?the?string
390?????????????SET?@s?=?SUBSTRING(@text,?@pos,?@i?-?@pos)
391?
392?????????????INSERT?INTO?@words
393?????????????VALUES?(@pos,?@s)
394??????????END?
395??????????SET?@pos?=?@i?+?1
396?
397??????????WHILE?@pos?<?LEN(@text)?
398?????????????AND?SUBSTRING(@text,?@pos,?1)?IN?('?',?',')
399?????????????SET?@pos?=?@pos?+?1?
400???????END?
401???????ELSE?
402???????BEGIN?
403??????????INSERT?INTO?@words?
404??????????VALUES?(@pos,?SUBSTRING(@text,?@pos,?LEN(@text)?-?@pos?+?1))
405?
406??????????SET?@pos?=?LEN(@text)?+?1?
407???????END?
408????END?
409????RETURN?
410?END
411?
412?
413?select?*
414?
415?from?table1?t1
416?
417?where?dbo.splitwords(t1.column1)
418?
419????like?dbo.splitwords(@userinput)
420?
421?(where?@userinput?is?a?input?parameter)
422?
423?--I?think?dbo.splitwords(t1.column1)?does?not?work?in?sql?server?2000.
424?
425?--I?hope?you?have?solution.?
426?
427?--Sample?table.
428?CREATE?TABLE?dbo.[tblRates]?(
429?[RateID]?[id]??,
430?[Rate]?[varchar]?(80)?COLLATE?Latin1_General_CI_AS?NULL
431?)
432?GO
433?
434?--Sample?data.
435?CREATE?TABLE?tblRates
436?(
437?RateID?INT,
438?Rate?VARCHAR(100)
439?)
440?GO
441?
442?INSERT?INTO?tblRates?(RateID,?Rate)
443?VALUES?(1,'1.123456789,2.1234567,3.12345')
444?
445?
446?--Current?query,?with?the?three?rates?split?out?(Fields:?Rate1,Rate2,Rate3)
447?SELECT
448?Rate?AS?'MasterRates'
449?,'Rate1'?=?LEFT(Rate,CHARINDEX(',',Rate)-1)
450?,'Rate2'?=
451?LEFT(SUBSTRING(Rate,(CHARINDEX(',',Rate)+1),LEN(Rate)),CHARINDEX(',',SUBSTRING(Rate,(CHARINDEX(',',Rate)+1),LEN(Rate)))-1)
452?,'Rate3'?=?SUBSTRING(Rate,?((LEN(LEFT(Rate,CHARINDEX(',',Rate)-1))+1)?+
453?(LEN(LEFT(SUBSTRING(Rate,(CHARINDEX(',',Rate)+1),LEN(Rate)),CHARINDEX(',',SUBSTRING(Rate,(CHARINDEX(',',Rate)+1),LEN(Rate)))-1))+1))+1
454?,LEN(Rate)?)
455?
456?--Fields?for?calculation?reference.
457?,'Rate1LengthIncDelimitter'?=?LEN(LEFT(Rate,CHARINDEX(',',Rate)-1))+1
458?,'Rate2LengthIncDelimitter'?=
459?LEN(LEFT(SUBSTRING(Rate,(CHARINDEX(',',Rate)+1),LEN(Rate)),CHARINDEX(',',SUBSTRING(Rate,(CHARINDEX(',',Rate)+1),LEN(Rate)))-1))+1
460?,'Rate3StartingCharacterPostion'?=
461?((LEN(LEFT(Rate,CHARINDEX(',',Rate)-1))+1)?+
462?(LEN(LEFT(SUBSTRING(Rate,(CHARINDEX(',',Rate)+1),LEN(Rate)),CHARINDEX(',',SUBSTRING(Rate,(CHARINDEX(',',Rate)+1),LEN(Rate)))-1))+1))+1
463?FROM?tblRates
464?
465?select?RateID,
466?replace(parsename(replace(replace(
467?Rate,?'.',?'/'),?',',?'.'),?3),?'/',?'.')?AS?'A',
468?replace(parsename(replace(replace(
469?Rate,?'.',?'/'),?',',?'.'),?2),?'/',?'.')?AS?'B',
470?replace(parsename(replace(replace(
471?Rate,?'.',?'/'),?',',?'.'),?1),?'/',?'.')??AS?'C'
472?from???tblRates
473?
474?CREATE?FUNCTION?[dbo].[SPLIT]?(
475?
476?@str_in?VARCHAR(8000),
477?
478?@separator?VARCHAR(4)?)
479?
480?RETURNS?@strtable?TABLE?(strval?VARCHAR(8000))
481?
482?AS
483?
484?BEGIN
485?
486?DECLARE
487?
488?@Occurrences?INT,
489?
490?@Counter?INT,
491?
492?@tmpStr?VARCHAR(8000)
493?
494?SET?@Counter?=?0
495?
496?IF?SUBSTRING(@str_in,LEN(@str_in),1)?<>?@separator
497?
498?SET?@str_in?=?@str_in?+?@separator
499?
500?SET?@Occurrences?=?(DATALENGTH(REPLACE(@str_in,@separator,@separator+'#'))?-?DATALENGTH(@str_in))/?DATALENGTH(@separator)
501?
502?SET?@tmpStr?=?@str_in
503?
504?WHILE?@Counter?<=?@Occurrences
505?
506?BEGIN
507?
508?SET?@Counter?=?@Counter?+?1
509?
510?INSERT?INTO?@strtable
511?
512?VALUES?(?SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))
513?
514?SET?@tmpStr?=?SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr)+1,8000)
515?
516?
517?IF?DATALENGTH(@tmpStr)?=?0
518?
519?BREAK
520?
521?
522?END
523?
524?RETURN
525?
526?END
527?
528?GO
529?
530?--
531?CREATE?FUNCTION?[dbo].[Split]
532?(
533?@pvcSearchString?VARCHAR(8000),
534?@pvcSeparator?VARCHAR(5)
535?)
536?
537?RETURNS?@strtable?TABLE?(strval?VARCHAR(8000))
538?
539?AS
540?
541?BEGIN
542?DECLARE
543?@tmpStr?VARCHAR(8000),
544?@intSeparatorLength?INT
545?
546?SET?@intSeparatorLength?=?LEN(@pvcSeparator)
547?
548?SET?@tmpStr?=?@pvcSearchString
549?WHILE?1=1
550?BEGIN
551?INSERT?INTO?@strtable?VALUES?(?SUBSTRING(@tmpStr,?0?,CHARINDEX(@pvcSeparator,@tmpStr)))
552?SET?@tmpStr?=?SUBSTRING(@tmpStr,CHARINDEX(@pvcSeparator,@tmpStr)+LEN(@pvcSeparator),8000)
553?IF?CHARINDEX(@pvcSeparator,@tmpStr)?<?1
554?BREAK
555?END
556?
557?RETURN
558?END
559?
560?--
561?/*
562?Sample?Delineated?Data?From?[Col1]:
563?"Item?1?|?Item?2?|?Item?3"
564?
565?Desired?Target?Result:
566?[ColItem1],[ColItem2],[ColItem3]
567?“Item?1”,?Item?2”,?Item?3”
568?
569?Use?the?MS?SQL?PARSENAME?function?to?achieve?this.?It’s?a?little?backwards?from?how?you?think?it?should?work,?but?follow?this?example?to?get?the?basic?idea.?PARSENAME?can?by?used?in?similar?fashion?to?a?split?function.
570?
571?Sample?Syntax?(default?delimited?style):
572?*/
573?Select
574?PARSENAME([Col1],3)?as?ColItem1
575?,PARSENAME([Col1],2)?as?ColItem2
576?,PARSENAME([Col1],1)?as?ColItem3
577?From?[myTable]
578?
579?--Sample?Syntax?(dealing?with?the?pipe?|?):
580?
581?Select
582?PARSENAME(replace([Col1],'|','.'),3)?as?ColItem1
583?,PARSENAME(replace([Col1],'|','.'),2)?as?ColItem2
584?,PARSENAME(replace([Col1],'|','.'),1)?as?ColItem3
585?From?[myTable]
586?
587?---split?column?with?part?of?text?string
588?CREATE?TABLE?myTable99(Col1?varchar(255))
589?GO
590?
591?SET?NOCOUNT?ON
592?INSERT?INTO?myTable99(Col1)
593?SELECT?'name:?johnson?email:?firstname:?philip?need:?doc?12'?UNION?ALL
594?SELECT?'name:?johnson?email:?pjohnson@yahoo.com?firstname:?philip?need:?doc?13'
595?GO
596?
597?CREATE?FUNCTION?udf_GetString(@Col1?varchar(255),?@tag?varchar(255))
598?RETURNS?varchar(255)
599?AS
600???BEGIN
601?DECLARE?@str?varchar(255)
602?????SELECT??????@str?=?SUBSTRING(@Col1
603?????????,?CHARINDEX(@tag,@Col1)+1+LEN(@tag)
604?????????,?CHARINDEX('?',Col1,CHARINDEX(@tag,@Col1)+1+LEN(@tag))-(CHARINDEX(@tag,@Col1)+1+LEN(@tag)))
605???????FROM?myTable99
606?????RETURN?@str
607???END
608?GO
609?
610?SELECT???????dbo.udf_GetString(Col1,'firstname:')
611?????,?dbo.udf_GetString(Col1,'email:')
612?????,?dbo.udf_GetString(Col1,'need:')
613?????,?Col1
614?FROM?myTable99
615?GO
616?
617?DECLARE?@tag?varchar(255)
618?SELECT?@tag?=?'firstname:'
619?????SELECT??????SUBSTRING(Col1
620?????????,?CHARINDEX(@tag,Col1)+1+LEN(@tag)
621?????????,?CHARINDEX('?',Col1,CHARINDEX(@tag,Col1)+1+LEN(@tag))-(CHARINDEX(@tag,Col1)+1+LEN(@tag)))
622???????FROM?myTable99
623?SELECT?@tag?=?'email:'
624?????SELECT??????SUBSTRING(Col1
625?????????,?CHARINDEX(@tag,Col1)+1+LEN(@tag)
626?????????,?CHARINDEX('?',Col1,CHARINDEX(@tag,Col1)+1+LEN(@tag))-(CHARINDEX(@tag,Col1)+1+LEN(@tag)))
627???????FROM?myTable99
628?SELECT?@tag?=?'need:'
629?????SELECT??????SUBSTRING(Col1
630?????????,?CHARINDEX(@tag,Col1)+1+LEN(@tag)
631?????????,?CHARINDEX('?',Col1,CHARINDEX(@tag,Col1)+1+LEN(@tag))-(CHARINDEX(@tag,Col1)+1+LEN(@tag)))
632???????FROM?myTable99
633?GO
634?
635?
636?SET?NOCOUNT?OFF
637?DROP?FUNCTION?udf_GetString
638?DROP?TABLE?myTable99
639?GO
640?
641?SELECT?*?FROM?[dbo].[SPLIT]('1,2,3')
642?
643?
644?CREATE?FUNCTION?[dbo].[SPLIT]
645?(
646?@Text?TEXT
647?)
648?RETURNS?@output?TABLE(
649?Item?INT
650?)
651?BEGIN
652?
653?DECLARE?@start?INT,?@end?INT?,?@Datalen?INT
654?
655?
656?SELECT?@start?=?1,
657?@end?=?CHARINDEX(',',?@Text),
658?@Datalen?=?DATALENGTH(@Text)?+?1
659?
660?WHILE?@start?<?@Datalen?BEGIN
661?IF?@end?<=?@start?BEGIN
662?SET?@end?=?@Datalen
663?END
664?
665?INSERT?INTO?@output?(Item)
666?VALUES?(SUBSTRING(@Text,?@start,?@end?-?@start))
667?
668?SET?@start?=?@end?+?1
669?SET?@end?=?CHARINDEX(',',?SUBSTRING(@Text,?@start,20))?+?@start?-1
670?END
671?RETURN
672?END
673?
674?
675?
676?And?here?get?2?fields
677?
678?SELECT?*?FROM?[dbo].[SPLIT_2]('1_1,2_1,2_2')
679?
680?CREATE?FUNCTION?[dbo].[SPLIT_2]
681?(
682?@Text?TEXT
683?)
684?RETURNS?@output?TABLE(
685?Document_id?INT,IndexOf?int
686?)
687?BEGIN
688?
689?DECLARE?@start?INT,?@end?INT?,?@Datalen?INT?,
690?@Values?VARCHAR(50),?@Document?VARCHAR(5),
691?@Index?VARCHAR(5),?@CharIndex?INT
692?
693?SELECT?@start?=?1,
694?@end?=?CHARINDEX(',',?@Text),
695?@Datalen?=?DATALENGTH(@Text)?+?1
696?
697?WHILE?@start?<?@Datalen?BEGIN
698?IF?@end?<=?@start?BEGIN
699?SET?@end?=?@Datalen
700?END
701?
702?SET?@Values?=?SUBSTRING(@Text,?@start,?@end?-?@start)
703?SET?@CharIndex?=?CHARINDEX('_',?@Values)
704?
705?SET?@Document?=?SUBSTRING(@Values,0,@CharIndex)
706?SET?@Index?=?SUBSTRING(@Values,@CharIndex+1,5)
707?
708?INSERT?INTO?@output?(document_id?,?IndexOf)
709?VALUES?(@Document,@Index)
710?
711?SET?@start?=?@end?+?1
712?SET?@end?=?CHARINDEX(',',?SUBSTRING(@Text,?@start,20))?+?@start?-1
713?END
714?RETURN
715?
716?
717?---SQL:?String?Split??Function
718?CREATE?FUNCTION?Split(@String?varchar(4000),?@Delimiter?char(1))
719?RETURNS?@Results?TABLE?(ID?int,?Items?nvarchar(4000))
720?AS
721?
722?BEGIN
723?????DECLARE?@INDEX?INT
724?????DECLARE?@SLICE?nvarchar(4000)
725?????DECLARE?@ID?int
726?
727?????SELECT?@INDEX?=?1,?@ID?=?1
728?????WHILE?@INDEX?!=0
729?
730?????????BEGIN
731??????????--?GET?THE?INDEX?OF?THE?FIRST?OCCURENCE?OF?THE?SPLIT?CHARACTER
732??????????SELECT?@INDEX?=?CHARINDEX(@Delimiter,@STRING)
733??????????--?NOW?PUSH?EVERYTHING?TO?THE?LEFT?OF?IT?INTO?THE?SLICE?VARIABLE
734??????????IF?@INDEX?!=0
735???????????SELECT?@SLICE?=?LEFT(@STRING,@INDEX?-?1)
736??????????ELSE
737???????????SELECT?@SLICE?=?@STRING
738??????????--?PUT?THE?ITEM?INTO?THE?RESULTS?SET
739??????????INSERT?INTO?@Results(ID,?Items)?VALUES(@ID,?@SLICE)
740??????????SELECT?@ID?=?@ID?+?1
741??????????--?CHOP?THE?ITEM?REMOVED?OFF?THE?MAIN?STRING
742??????????SELECT?@STRING?=?RIGHT(@STRING,LEN(@STRING)?-?@INDEX)
743??????????--?BREAK?OUT?IF?WE?ARE?DONE
744??????????IF?LEN(@STRING)?=?0?BREAK
745?????????END
746?????RETURN?
747?
748?select?Items?from?dbo.Split(@List,?',')
749?
750?Create?FUNCTION?Split(@String?varchar(4000),?@Delimiter?char(1))
751?RETURNS?@Results?TABLE?(ID?int,?Items?nvarchar(4000))
752?AS
753?
754?BEGIN
755?DECLARE?@INDEX?INT
756?DECLARE?@SLICE?nvarchar(4000)
757?DECLARE?@ID?int
758?
759?SELECT?@INDEX?=?1,?@ID?=?1
760?WHILE?@INDEX?!=0
761?
762?BEGIN
763?--?GET?THE?INDEX?OF?THE?FIRST?OCCURENCE?OF?THE?SPLIT?CHARACTER
764?SELECT?@INDEX?=?CHARINDEX(@Delimiter,@String)
765?--?NOW?PUSH?EVERYTHING?TO?THE?LEFT?OF?IT?INTO?THE?SLICE?VARIABLE
766?IF?@INDEX?!=0
767?SELECT?@SLICE?=?LEFT(@String,@INDEX?-?1)
768?ELSE
769?SELECT?@SLICE?=?@String
770?--?PUT?THE?ITEM?INTO?THE?RESULTS?SET
771?INSERT?INTO?@Results(ID,?Items)?VALUES(@ID,?@SLICE)
772?SELECT?@ID?=?@ID?+?1
773?--?CHOP?THE?ITEM?REMOVED?OFF?THE?MAIN?STRING
774?SELECT?@String?=?RIGHT(@String,LEN(@String)?-?@INDEX)
775?--?BREAK?OUT?IF?WE?ARE?DONE
776?IF?LEN(@String)?=?0?BREAK
777?END
778?Return
779?END
780?
781?

總結

以上是生活随笔為你收集整理的SQL字符串处理--按分割符拆分字符串T-SQL to Split a varchar into Words的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。