function to replace comma, then loop

Posted on 2007-10-19
Last Modified: 2008-01-09
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.
Question by:nito8300
    LVL 15

    Expert Comment

    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>

    LVL 15

    Accepted Solution

    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))
          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
                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)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now