subtraction of prime zeros from an alphanumeric field-CodePudding

Good evening ,

I have an alphanumeric field and I want after the letters to subtract the zeros that it has

my base is azure and my field is a bit like that, the number is 10 digits but I want to subtract the zeros to make it smaller in report. my field is SALDOC.RELDOCSDATE DEAP0000013169 3/12/2021

CodePudding user response：

If your database support REGEXP_REPLACE, then you may try:

SELECT REGEXP_REPLACE('GFRE000005268', '([A-Z] )0*([1-9][0-9]*)', '\1\2')

Here is a MySQL demo. The actual syntax on your database might be slightly different.

CodePudding user response：

It would depend on the requirement. You can use this if you are sure that there is always just 4 letter prefix.

This would just convert the numbers to INT to remove those zeros from the start.

SELECT CONCAT(LEFT('GFRE000005268',4),CONVERT(int,(REPLACE('GFRE000005268',LEFT('GFRE000005268',4),''))))

CodePudding user response：

my base is azure and my field is a bit like that, the number is 10 digits but I want to subtract the zeros to make it smaller in report. my field is SALDOC.RELDOCSDATE DEAP0000013169 3/12/2021