0

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.

graphene
  • 109
  • 1
  • 8
  • 1
    Regex is probably the best, or at least very suitable, tool for doing this, but SQL Server does not have much regex support. I might try to handle this outside of SQL Server. – Tim Biegeleisen Oct 10 '18 at 09:32
  • 1
    @graphene Hi, look at [T H I S](https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string) – Nifriz Oct 10 '18 at 09:34
  • The provided link only gives the numeric part, which I already have in the select part. I need also the leading zeroes. – graphene Oct 10 '18 at 09:36
  • @graphene you didn't explain what you want, or what the problem is. For one thing, FORMAT doesn't work with strings. You'd have to `CAST` that string to `int` first – Panagiotis Kanavos Oct 10 '18 at 09:38

3 Answers3

3

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;
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • there is one problem: it will ignore code with 8746 for example. I added one more example above. – graphene Oct 10 '18 at 09:46
  • 1
    @graphene, I ran the code on my machine and I get `008746` instead of `NULL`. Let me see if I can figure out why you are getting `NULL`. – Dan Guzman Oct 10 '18 at 09:52
2

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')
Eralper
  • 6,461
  • 2
  • 21
  • 27
  • When I use UPDATE table_A SET Code=(select dbo.fnUdfLeftSQLPadding(Code,6,'0') FROM Table_A) gives an error (yes I have created the function first). I cannot use TOP 1 as it would update all rows with 033404. – graphene Oct 10 '18 at 09:42
  • 1
    @graphene that's a problem with the query itself. There's no reason to use a SELECT inside FORMAT – Panagiotis Kanavos Oct 10 '18 at 09:45
  • I added a second query and a second function named udfLeftSQLPadding that removes non-numeric values and gets only the numeric data – Eralper Oct 10 '18 at 10:21
1

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
Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
  • 1
    @graphene it returns `00008746`, not null. I copy/pasted the actual results. Have you tried running *this* script? Perhaps your table contains something that looks like 8746 but has other leading/trailing characters? – Panagiotis Kanavos Oct 10 '18 at 09:53
  • yes. It is working. Thanks. Yours and Dan Guzman scripts are working. – graphene Oct 10 '18 at 09:56