• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

function to replace comma, then loop

hello all,
I'm looking to have a user defined function or a function i can include in a new field in the table itself based on the existing field named: code

i have a table with a field with the following data:
code = "AB, BC, CD"
this field has variable length. so there could be x number of codes.

i would like to convert the field to: <a href='mypage.asp?code=' & code1 & "'>" & code1 & "</a>"
and then loop thru all codes.

thank you all.
0
nito8300
Asked:
nito8300
  • 2
1 Solution
 
dbbishopCommented:
So, in your above example, are you saying you want the final result to be:
<a href='mypage.asp?code=AB'>AB</a><a href='mypage.asp?code=BC'>BC</a><a href='mypage.asp?code=CD'>CD</a>

0
 
dbbishopCommented:
This function returns a table variable. You use it as:

dbo.SplitList(code, ',')

In a real example, you would code:

DECLARE @html VARCAHR(8000)

SELECT @html = coalesce(@html, '') + '<a href="mypage.asp?code=' + ListItem + '">' + ListItem + '</a>'
FROM dbo.SplitList(@code, ',')

@html will have the concatenated code.

=========================================================
CREATE  FUNCTION SplitList (@List VARCHAR(4000), @Delimiter CHAR(1) = ',')
       RETURNS @tblList TABLE (ListItem VARCHAR(100), UNIQUE CLUSTERED (ListItem))
AS
   BEGIN
      DECLARE @pos INT
      DECLARE @ListItem VARCHAR(4000)
     
      SET @List = LTRIM(RTRIM(@List))
      IF RIGHT(@List, 1) = @Delimiter
          SET @List = LEFT(@List, Len(@List) - 1)
      
      SET @pos = CHARINDEX(@Delimiter, @List)

      WHILE @pos > 0
          BEGIN
            SET @ListItem = LEFT(@List, CHARINDEX(@Delimiter, @List) - 1)
            INSERT INTO @tblList (ListItem) VALUES(RTRIM(LTRIM(@ListItem)))
            SET @List = SUBSTRING(@List, @pos + 1, LEN(@List))
            SET @pos = CHARINDEX(@Delimiter, @List)
          END
      RETURN
    END
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now