CODEWITHSUNDEEP
sql-serverstring
M. Rain
M. Rain

Reputation: 325

How to format string custom formatting in SQL Server 2008

Custom string formatting in SQL Server 2008

DECLARE @a varchar(11)
SET @a='40010510120'

Result should be 

|4|0|0|1|-|0|5|1|-|0|1|-|2|0|

or

4 0 0 1 - 0 5 1 - 0 1 - 2 0

How can I do this?

Thanks in advance!

Upvotes: 2

Views: 5094

Answers (2)

yogesh
yogesh

Reputation: 23

Try this...

DECLARE @a varchar(11)
SET @a='40010510120'

Declare @i as int
set @i=1
declare @output as varchar(29)
set @output=''
while @i<=LEN(@a)
begin

 if @i=5 or @i=8 or @i=10
    set @output= @output + '|-|' + SUBSTRING(@a,@i,1)
 else if @i=LEN(@a)
    set @output= @output + '|' + SUBSTRING(@a,@i,1) + '|'
 else
    set @output= @output + '|' + SUBSTRING(@a,@i,1)

set @i=@i+1
end
select @a
select @output

Upvotes: 1

Szymon
Szymon

Reputation: 43023

That's quite a lot of concatenation but it will work fine if the string length is 11:

DECLARE @a varchar(11)
SET @a='40010510120'

SELECT '|' + SUBSTRING(@a, 1, 1)
    + '|' + SUBSTRING(@a, 2, 1)
    + '|' + SUBSTRING(@a, 3, 1)
    + '|' + SUBSTRING(@a, 4, 1)
    + '|-'
    + '|' + SUBSTRING(@a, 5, 1)
    + '|' + SUBSTRING(@a, 6, 1)
    + '|' + SUBSTRING(@a, 7, 1)
    + '|-'
    + '|' + SUBSTRING(@a, 8, 1)
    + '|' + SUBSTRING(@a, 9, 1)
    + '|-'
    + '|' + SUBSTRING(@a, 10, 1)
    + '|' + SUBSTRING(@a, 11, 1)
    + '|'

You can turn it into a function:

CREATE FUNCTION dbo.CustomFormat(@a NVARCHAR(11))
RETURNS varchar(29)
AS
BEGIN
RETURN '|' + SUBSTRING(@a, 1, 1)
        + '|' + SUBSTRING(@a, 2, 1)
        + '|' + SUBSTRING(@a, 3, 1)
        + '|' + SUBSTRING(@a, 4, 1)
        + '|-'
        + '|' + SUBSTRING(@a, 5, 1)
        + '|' + SUBSTRING(@a, 6, 1)
        + '|' + SUBSTRING(@a, 7, 1)
        + '|-'
        + '|' + SUBSTRING(@a, 8, 1)
        + '|' + SUBSTRING(@a, 9, 1)
        + '|-'
        + '|' + SUBSTRING(@a, 10, 1)
        + '|' + SUBSTRING(@a, 11, 1)
        + '|'
END

SELECT dbo.CustomFormat('40010510120')

Upvotes: 1

Related Questions