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!
2 Answers
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')
Comments
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
FORMATfunction, but that's a new feature in the 2012 release, not available in earlier versions