CODEWITHSUNDEEP
sqlpadding
NITHIN N
NITHIN N

Reputation: 35

Pad zeros for nvarchar column in table

I want to add zero for only single digit value before the dot (.) When i use Input:

1.3.45 TU 3
1.2.5 TU 8

Expected Output:

01034503
01020508

Current query:

select REPLACE(
                replace(
                            replace(@Column,'TU','')   -- remove TU
                        ,'.',''   -- remove dot 
                        )
                ,' ','') -- remove space
from Table;

Current Output:

13453
1258

Upvotes: 0

Views: 105

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81940

If SQL Server, you can use a Split/Parse function to normalize the string

Declare @YourTable Table (YourField varchar(25))
Insert Into @YourTable values 
('1.3.45 TU 3'),
('1.2.5 TU 8')

Select A.*
      ,NewField = B.String
 From  @YourTable A
 Cross Apply (
       Select String = ltrim((Select cast(RetVal as varchar(25)) 
         From (Select RetSeq,RetVal=Right('00'+RetVal,2) 
               From   [dbo].[udf-Str-Parse](replace(YourField,' ','.'),'.') 
               Where  Try_Convert(int,RetVal)>=0 ) A
         For XML Path ('')))
       ) B

Returns

YourField      NewField
1.3.45 TU 3    01034503
1.2.5 TU 8     01020508

The UDF if needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From (Select x = Cast('<x>'+ Replace(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Where are the zeros? You want something like this:

select ('0' +                                       -- initial zero
        replace(replace(replace(@Column, 'TU', ''   -- remove TU
                               ), '.', '0'          -- replace dot with zero 
                        ), ' ', ''
               )                                    -- remove space
       )
from Table;

Upvotes: 0

Related Questions