big problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

big problem

Author

Topic

adiboy0420
Starting Member

10 Posts

Posted - 2012-07-13 : 01:07:41

how do i create a program which take two paramates,

first paramater is a varchar(5000), it will take a input string that is
seperated with a delimiter( but could be any delimiter)

second paramater is varchar(1)-which is the delimiter

ex

so if input '1,2,200,abc,xyz' --- so the delimiter is ','

so first paramater would be '1,2,200,abc,xyz'
and second parmater would be ','

answer needs to be
1
2
200
abc
xyz
,

but now also i can come back and put in the same program

so if input '1:2:200:abc:xyz' --- so the delimiter is ':'

so first paramater would be '1:2:200:abc:xyz'
and second parmater would be ':'

and answer needs to be now
1
2
200
abc
xyz
:

--------------------------------------------------------------------------------

how do i do that :(

------------ i have this so far

@a varchar(5000)
As

declare @x varchar(5000)
declare @y varchar (2000)
declare @z varchar(1000)

set @x =@a

set @y=@x+','

while(CHARINDEX(',',@y)>0)

begin

set @z= LEFT(@y,charindex(',',@y)-1)

select @z

set @y= RIGHT(@y,len(@y)-len(@z)-1)

end

but the code i have will only work with ','

so how i get it to pick up the if someone picks another delimiter

adiboy0420
Starting Member

10 Posts

Posted - 2012-07-13 : 01:11:55

also you think i can just do if, else clause on this ?

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-07-13 : 01:50:47

Hi
try this

declare @str as varchar(5000)
declare @y as char(1)
set @y=','
--set @y=':'
set @str='1,2,200,abc,xyz'
--set @str='1:2:200:abc:xyz'
DECLARE @xml xml

SET @str = '<root><row>' + REPLACE(@str,@y,'</row><row>') + '</row><row>'+@y+'</row></root>'
SET @xml = CAST(@str AS XML)
SELECT tab.col.value ('.','varchar(50)')
FROM @xml.nodes('/root/row') tab(col)

S

nathans
Aged Yak Warrior

938 Posts

Posted - 2012-07-13 : 02:08:20

Please see discussion on "Splitting Strings" here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

adiboy0420
Starting Member

10 Posts

Posted - 2012-07-13 : 10:23:10

thanks for the help stepson

but in the code, the use can put any of these

{
}
<
>
"
'
?
%
*
/
,

and it needs to run

so how should i do... should i repeat SELECT tab.col.value ('.','varchar(50)')
with all the delimiters?

-----------

nathans thanks.. i will read it

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-13 : 17:07:08

So... The first character not in the range of {a-z0-9} is the delimiter?
If this is true, you can write the function with autosense.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources