Append a specific character after each character of a string in sql server

Question

Thanks to advise me for the below issue:

I am using below query to fetch the value of a column:

Select OptionList = case  when isnull(AS_CIS_Code,'') <> '' then AS_CIS_Code   
   else ''  
   end 
from added_services

AS_CIS_Code column is of varchar(10) in added_services table. It contains values like 'AB', 'ABC', 'GHKIK', 'UYTIOPJ' and so on which represents different codes.

Now I have to select these codes after modifying the above query so that '_' is appended after each character.

Like it should be fetched as 'A_B_', 'A_B_C_', 'G_H_K_I_K_', 'U_Y_T_I_O_P_J_'.

How should I implement it? Using a temp table will down the performance for one column only, so should I use while loop or please suggest me better alternatives.

No I have to do it in DB itself, I am sending this value using view to another application. — Shivam Gupta
– Shivam Gupta, Commented Jul 24, 2014 at 9:05

Jesuraja · Accepted Answer · 2014-07-24 09:29:58Z

3

Try this:

DECLARE @Input VARCHAR(100) = 'TESTING'
DECLARE @Pos INT = LEN(@Input)
WHILE @Pos > 1
BEGIN

    SET @Input = STUFF(@Input,@Pos,0,'_')
    SET @Pos = @Pos - 1
END

SELECT @Input

Output

T_E_S_T_I_N_G

UDF

CREATE FUNCTION PadStr(@Data VARCHAR(100)) RETURNS VARCHAR(200)
AS
BEGIN
    DECLARE @Input VARCHAR(200) = @Data 
    DECLARE @Pos INT = LEN(@Input)
    WHILE @Pos > 1
    BEGIN

        SET @Input = STUFF(@Input,@Pos,0,'_')
        SET @Pos = @Pos - 1
    END

    RETURN @Input + '_'
END

Output

SELECT dbo.PadStr('TESTING')      -- T_E_S_T_I_N_G_

edited Jul 24, 2014 at 9:29

answered Jul 24, 2014 at 9:07

Jesuraja

3,8524 gold badges26 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mikael Eriksson · Accepted Answer · 2014-07-24 09:49:28Z

0

You can split the string using a numbers table and the rebuild it using for xml path().

select isnull(C.Value, '') as AS_CIS_Code 
from added_services as A
  cross apply (
              select substring(A.AS_CIS_Code, T.N, 1)+'_'
              from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) as T(N)
              where T.N <= len(A.AS_CIS_Code)
              order by T.N
              for xml path('')
              ) as C(Value)

SQL Fiddle

answered Jul 24, 2014 at 9:49

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Comments

Azar · Accepted Answer · 2014-07-24 09:11:39Z

0

Try this

Create Function Code_Pad
(
    @code varchar(max) 
)
returns varchar(max)
as
begin

Declare @coding varchar(max) =''
Declare @i int = 1

      WHILE(LEN(@Coding)<=2*len(@code)-1)
      begin
        Select @coding = @coding+SUBSTRING(@code,@i,1)+'_'
        set @i=@i+1
      end
return @coding
end

End of Function

Select OptionList = case  when isnull(AS_CIS_Code,'') <> '' then dbo.Code_Pad(AS_CIS_Code)   else ''  
   end 
from added_services

edited Jul 24, 2014 at 9:11

answered Jul 24, 2014 at 9:06

Azar

1,86715 silver badges18 bronze badges

Comments

Kiran Hegde · Accepted Answer · 2014-07-24 09:59:39Z

0

May not be best solution, but works in one query using recursion

;WITH valCTE(Replaced,ToBeReplaced,Position)
AS
(
    SELECT CAST(LEFT(AS_CIS_Code,1) + '_' AS VARCHAR(20))
          ,SUBSTRING(AS_CIS_Code,2,LEN(AS_CIS_Code)-1)
          ,1
    FROM added_services

    UNION ALL
    SELECT CAST(Replaced + LEFT(ToBeReplaced,1) + '_' AS VARCHAR(20))
        ,SUBSTRING(ToBeReplaced,2,LEN(ToBeReplaced)-1)
        ,Position+1
    FROM valCTE
    WHERE LEN(ToBeReplaced)>0 
)
SELECT TOP 1 LEFT(Replaced,LEN(Replaced)-1) -- remove last _ 
FROM valCTE
ORDER BY Position DESC

edited Jul 24, 2014 at 9:59

answered Jul 24, 2014 at 9:18

Kiran Hegde

3,6811 gold badge18 silver badges14 bronze badges

Collectives™ on Stack Overflow

Append a specific character after each character of a string in sql server

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related