Function for delimiters - 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 2000 Forums

Transact-SQL (2000)

Function for delimiters

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-04-19 : 15:45:22

Guys,

I have created function where I pass delimiter string and it gets me the charecters after the delimiter.
I have address field which I need to split to city and state.

I have the following examples

VA Beach, VA 23542 to VA Beach and VA
VIRGINIA BEACH, VA to VIRGINIA BEACH and VA

-- @src source string
-- @dlm delimiter
-- @ind token's number, i.e. 1st, 2nd token, ...
CREATE function getToken(@src varchar(2000), @dlm varchar(2000), @ind int)
returns varchar(2000)
as
begin
declare @pos int,
@posn int,
@cnt int,
@token varchar(2000)
set @pos=1
set @cnt=1
set @src=@src+@dlm
while @cnt<=@ind
begin
set @posn=charindex(@dlm,@src, @pos)
if @posn>0
select @token=substring(@src, @pos, @posn-@pos)
else
return null
set @pos=@posn+len(@dlm+'1')-1
set @cnt=@cnt+1
end
if @@error<>0 or len(@token)=0
return null
return @token
end

SELECT DBO.GETTOKEN('VA Beach, VA 23542', ',', 1)

I need result VA instead of VA 23542

Any suggestions/inputs would help.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-19 : 19:38:10

[code]

declare @var varchar(200)

select @var = 'VA Beach, VA 23542'

select @var,
dbo.fnParseString(-1, ',', @var),
dbo.fnParseString(-1, ' ', ltrim(dbo.fnParseString(-2, ',', @var)))
[/code]

using fnParseString from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources