function - 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 2005 Forums

Transact-SQL (2005)

function

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-11-20 : 07:05:38

This is a function which returns a single letter code for a passed in source_code. how can this be modified to pass in several source_codes separated by comma - see below

ALTER FUNCTION [dbo].[fnSourceShortCode]
(
@SourceCode VARCHAR(20)
)
RETURNS varchar(10)
BEGIN
declare @SourceShortCode char(1)

select
@SourceShortCode =
case
when @SourceCode = 'NFBE' then 'N'
when @SourceCode = 'TTFN03' then 'L'
when @SourceCode = 'GFI01' then 'G'
when @SourceCode = 'QLABND1' then 'Q'
when @SourceCode = '4GCW' then 'I'
when @SourceCode = 'TR73' then 'T'
when @SourceCode = 'EUROVCSC11' then 'B'
end

return @SourceShortCode

END

select dbo.fnSourceShortCode('EUROVCSC11')

return 'B'

This is what I would like to achieve:

select dbo.fnSourceShortCode('EUROVCSC11,GFI01,TR73')

to return:
'B,G,T'

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-20 : 07:31:27

Declare the parameter to be of a long type say varchar(8000)
and pass comma seperated values to it. In sp, make use of string functions LEFT,RIGHT,PATINDEX to extract each value from string using commas as reference and repeat the current code for each extracted item. You can refer to books online for syntaxes and example usages of functions.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-20 : 07:38:36

[code]DECLARE @SourceShortCode varchar(100)

SELECT @SourceShortCode = ISNULL(@SourceShortCode + ',', '') +
CASE Data
WHEN 'NFBE' THEN 'N'
WHEN 'TTFN03' THEN 'L'
WHEN 'GFI01' THEN 'G'
WHEN 'QLABND1' THEN 'Q'
WHEN '4GCW' THEN 'I'
WHEN 'TR73' THEN 'T'
WHEN 'EUROVCSC11' THEN 'B'
END
FROM dbo.fnParseList(',', 'EUROVCSC11,GFI01,TR73')

SELECT @SourceShortCode[/code]

KH
[spoiler]Time is always against us[/spoiler]

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-11-20 : 13:02:27

You are aware that the next question from arkiboys is going to be something along the lines of "I have this column that has several comma-separated values, like 'B,G,T'. How do I separate them out into individual values?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-20 : 20:47:24

quote:
Originally posted by KenW

You are aware that the next question from arkiboys is going to be something along the lines of "I have this column that has several comma-separated values, like 'B,G,T'. How do I separate them out into individual values?

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-21 : 01:03:44

quote:
Originally posted by KenW

You are aware that the next question from arkiboys is going to be something along the lines of "I have this column that has several comma-separated values, like 'B,G,T'. How do I separate them out into individual values?

Then, Tan would refer other function

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources