| Author |
 Topic  |
|
|
adiboy0420
Starting Member
USA
10 Posts |
 Posted - 07/13/2012 : 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
USA
10 Posts |
Posted - 07/13/2012 : 01:11:55
|
also you think i can just do if, else clause on this ?
|
 |
|
|
stepson
Yak Posting Veteran
Romania
85 Posts |
Posted - 07/13/2012 : 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
|
Edited by - stepson on 07/13/2012 01:58:34 |
|
|
|
nathans
Aged Yak Warrior
USA
933 Posts |
|
|
adiboy0420
Starting Member
USA
10 Posts |
Posted - 07/13/2012 : 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
Sweden
29138 Posts |
Posted - 07/13/2012 : 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" |
|
|
| |
Topic |
|
|
|
big problem
