CODEWITHSUNDEEP
sqlsql-serverfunctiont-sqlleading-zero
Gregory Brauninger
Gregory Brauninger

Reputation: 75

Removing Leading Zeros Part 2

SQL Server 2012

I have 3 columns in my table that will be using a function. '[usr].[Udf_OverPunch]'. and substring.

Here is my code:

[usr].[Udf_OverPunch](SUBSTRING(col001, 184, 11)) as REPORTED_GAP_DISCOUNT_PREVIOUS_AMOUNT

This function works appropriately for what I need it to do. It is basically converting symbols or letters to a designated number based on a data dictionary.

The problem I am having is that there are leading zeros. I just asked a questions about leading zeroes but it won't allow me to do it with the function columns because of the symbols cannot be converted to int.

This is what I am using to get rid of leading zeros (but leave one zero) in my code for the other columns:

cast(cast(SUBSTRING(col001, 217, 6) as int) as varchar(25)) as PREVIOUS_REPORTING_PERIOD

This works well at turning a value of '000000' to just one '0' or a value of '000060' to '60' but will not work with the function because of the symbol or letter (when trying to convert to int).

As I mentioned, I have 3 columns which produce values that look something like this when the function is not being used:

'0000019753{'
'0000019748G'
'0000019763H'

My goal here is to use the function while also removing the leading zeros (unless they are all zeros then keep one zero).

This is what I attempted that isn't working because the value contains a character that isn't an integer:

[usr].[Udf_OverPunch]cast(cast(SUBSTRING(col001, 184, 6) as int) as varchar(25)) as REPORTED_GAP_DISCOUNT_PREVIOUS_AMOUNT,

Please let me know if you have any ideas or need more information. :)

Upvotes: 1

Views: 109

Answers (3)

KumarHarsh
KumarHarsh

Reputation: 5094

try this,

declare @i varchar(50)='0000019753}'--'0000019753'

select case when ISNUMERIC(@i)=1 then 
cast(cast(@i as bigint) as varchar(50)) else @i end

or

[usr].[Udf_OverPunch]( case when ISNUMERIC(col001)=1 then 
cast(cast(col001 as bigint) as varchar(50)) else col001 end)

Upvotes: 0

gotqn
gotqn

Reputation: 43666

I am handling such replacement with T-SQL CLR function that allows replacement using regular expressions. So, the solution will be like this:

[dbo].[fn_Utils_RegexReplace] ([value], '^0{1,}(?=.)', '')

You need to create such function because there are no regular expression support in T-SQL (build-in).

How to create regex replace function in T-SQL?

For example:

enter image description here

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44991

select      case when col like '%[^0]%' then substring(col,patindex('%[^0]%',col),len(col)) when col like '%0%' then '0' else col end   

from        tab

or

select      case when col like '%[^0]%' then right(col,len(ltrim(replace(col,'0',' ')))) when col like '%0%' then '0' else col end

from        tab

Upvotes: 1

Related Questions