CODEWITHSUNDEEP
sqlsql-serversql-server-2012
graphene
graphene

Reputation: 157

Leading zeros after getting numeric part of string

SQL Server 2012. Need to get numeric part with 6 figures, if not enough figures fill with leading zeros. Code is nvarchar type.

TABLE_A Before update

Id  Code
1  s33404tft 
2  dd345ui
3  456567t
4  8746

TABLE_A After Update

Id  Code
1  033404
2  000345
3  456567
4  008746

sql script:

 Update table_A
    SET Code=FORMAT((SELECT SUBSTRING(code, PATINDEX('%[0-9]%', code), PATINDEX('%[0-9][^0-9]%', code + 't') - PATINDEX('%[0-9]%', code) + 1) AS Number 
              FROM Table_A),'000000')

It does not work.

Upvotes: 0

Views: 151

Answers (3)

Eralper
Eralper

Reputation: 6612

You can use the replicate function for padding leading zeros in SQL

Please check the referred document for the USD function udfLeftSQLPadding Then you can use it in following format:

select dbo.udfLeftSQLPadding('12345',6,'0')

Additionally, to fetch only the numeric values may be you can use ClearNonNumericCharacters UDF where I shared at Remove Non-Numeric Character

So your script will be as follows

Update table_A
set 
Code = dbo.udfLeftSQLPadding( dbo.ClearNonNumericCharacters(Code), 6, '0')

Upvotes: 2

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131189

FORMAT doesn't work with text types. The extracted numeric part would have to be converted to an integer first in order to format it, eg :

update Table_A
set code=FORMAT(cast( SUBSTRING(code, PATINDEX('%[0-9]%', code), PATINDEX('%[0-9][^0-9]%', code + 't') - PATINDEX('%[0-9]%', code) + 1) 
                      as int)
                ,'00000000')

For example :

declare @table_A table (ID int, Code nvarchar(20))
insert into @table_A (ID,Code)
values
(1,'s33404tft'),
(2,'dd345ui'),
(3,'456567t'),
(4,'8746');

update @table_A
set code=FORMAT(cast(SUBSTRING(code, PATINDEX('%[0-9]%', code), PATINDEX('%[0-9][^0-9]%', code + 't') - PATINDEX('%[0-9]%', code) + 1) as int)
                ,'00000000')


select * from @table_A

Produces : 

ID  Code
1   00033404
2   00000345
3   00456567
4   00008746

Upvotes: 1

Dan Guzman
Dan Guzman

Reputation: 46203

One way is concatenating leading zeros and using RIGHT to extract the desired value:

UPDATE table_A
SET Code =
    RIGHT('000000' + SUBSTRING(code, PATINDEX('%[0-9]%', code), PATINDEX('%[0-9][^0-9]%', code + 't') - PATINDEX('%[0-9]%', code) + 1), 6)
    FROM Table_A;

Upvotes: 3

Related Questions