Is there a sql function to split strings?????

• chileu17

  Hall of Fame

  Points: 3093

  More actions

  May 19, 2008 at 4:34 pm

  #185803

  Hi everyone, I have found myself in a hurry of needing to find a sql server function to split words? Like the split function in C#??

  For example if I pass to the function a sentence, it should return just the words :hehe: or how could I do this? maybe using a cursor?? or a temp table? Any ideas or suggestions are accepted Thanks in advance.

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  May 19, 2008 at 4:41 pm

  #817518

  Search this site for 'split' and you'll find a number of split functions.

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

• antonio.collins

  SSCrazy Eights

  Points: 9600

  More actions

  May 19, 2008 at 7:53 pm

  #817544

  i've become very dependent on this:

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER function [dbo].[fListToVarchars]( @list varchar(max), @delim varchar(6) )

  returns @returnTable table

  ( item varchar(255) not null, itemSequence smallint not null )

  as begin

  declare @xml XML

  set @xml = char(60)+'item>' + REPLACE(REPLACE(@list,char(60),'<'),@delim,char(60)+'/item>'+char(60)+'item>') + char(60)+'/item>'

  insert into @returnTable

  SELECT data.item.value('.','varchar(255)'), row_number() over (order by getdate())

  FROM @xml.nodes('//item') as data(item)

  return

  end

  ;

  usage:

  select * from dbo.fListToVarchar('mary had a little lamb.',' ');

• Jeff Moden

  SSC Guru

  Points: 1004614

  More actions

  May 20, 2008 at 1:02 am

  #817605

  I've found XML splits to be just a tad on the slow side. Take a look at the following URL.

  http://www.sqlservercentral.com/articles/TSQL/62867/

  Also, fairly big article on splits coming out on Wednesday.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)