You must Sign In to post a response.
  • Category: SQL Server

    How to add prefix in sql server 2012

    Hi All,

    I have a SQL value like 2,3,4

    I want the output like R2, R3, R4

    Please help how can I do this.

    Thanks,
    Ram Prasad
  • #769326
    Hi Ram,

    You need to split these values, then concat your suffix/Prefix and again concat the values using comma

    Sql server does not have any inbuilt function to split the comma separated values. So you can create a split function in ur database.It will be useful in ur future queries also.Use below code for the same

    --------------------Function to split comma delimeted values--------------------------

    CREATE FUNCTION SplitString
    (
    @Input NVARCHAR(MAX),
    @Character CHAR(1)
    )
    RETURNS @Output TABLE (
    Item NVARCHAR(1000)
    )
    AS
    BEGIN
    DECLARE @StartIndex INT, @EndIndex INT

    SET @StartIndex = 1
    IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
    BEGIN
    SET @Input = @Input + @Character
    END

    WHILE CHARINDEX(@Character, @Input) > 0
    BEGIN
    SET @EndIndex = CHARINDEX(@Character, @Input)

    INSERT INTO @Output(Item)
    SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

    SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
    END

    RETURN
    END
    GO

    -------------------------------Logic to add Prefix ---------------------------

    DECLARE @List VARCHAR(8000)

    SELECT @List = COALESCE(@List + ',', '') +'R'+ CAST(Item AS VARCHAR)
    FROM dbo.SplitString('1,2,2,4', ',')

    SELECT @List
    ---------------------------------------------------------------------------------

    Input:
    1,2,3,4
    -------
    OUTPUT :
    R1,R2,R2,R4

    ~ Cheers!!

    Thanks!
    Anjali Bansal

    ~Give your best and lead the world


  • Sign In to post your comments