Pasrenames

[Pauldohert]

I was using parsenames to split a string like

'12.23.34.567' into 4 separate ints. But its limited to 4.

Has anyone got a good alternative to allow more than 4 splits?

Ta

 

[SQL_Hell]

Hi,

What is parsenames?

Will the dots always be in the same place in the string?

 

[Pauldohert]

Sorry Parsename

http://doc.ddart.net/mssql/sql70/pa-pt.htm

is limited to 4 splits - I want something that allows more than 4?

Ta

 

[SQL_Hell]

Interesting, I have never seen the parsename() function before.

So what is the actual string you are trying to cut up? cant be an Ip address.

I think you're gonna have to do it the hard way I'm afraid

This will involve using CHARINDEX() to get the location of the dots in the string, then use SUBSTRING() to cut the string up.

 

[Pauldohert]

No its just a list of int PKs - that I want to action in someway.

 

[FoFa]

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO
--Test
select * from fnSplit('1,22,333,444,,5555,666', ',')
select * from fnSplit('1##22#333##444','##')  --note second item has embedded #
select * from fnSplit('1 22 333 444  5555 666', ' ')

 

[Pauldohert]

Thanks

I may well not understand -

But that retruns a table? How do I get out say - this 3rd item in the list/table.

If I ran this thru a looped bit of code I would make the table n times wouldb'yt I , which seems a little inefficient?