1

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. :)

Gregory Brauninger
  • 75
  • 7
  • Desperately need some advice here. This needs to be done by tomorrow. It is the only thing left for the entire process to be finished. – Gregory Brauninger Nov 03 '16 at 21:24
  • I've answered your question, however, there might be a much cleaner solution depending on your data. Is it always in the same length? is is always composed from digits and + a single non-digit character at the end? – David דודו Markovitz Nov 03 '16 at 23:09

3 Answers3

1
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
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
  • I'm having trouble getting my function to work with this. – Gregory Brauninger Nov 04 '16 at 14:03
  • @GregoryBrauninger - This is a tested code. Could you please elaborate? – David דודו Markovitz Nov 04 '16 at 14:11
  • @GregoryBrauninger, there is nothing I can do with that information. Do you get an error? Do you get an unexpected results? can you give a sample string? – David דודו Markovitz Nov 04 '16 at 14:21
  • I'm trying to format that comment correctly. The leading zeros and leaving only one zero is working perfectly. The problem is my function isn't being executed so I'm left with: @Dudu '13962{' '17801H' '2969G' – Gregory Brauninger Nov 04 '16 at 14:21
  • Everything is great I just need it to use my function ([usr].[Udf_OverPunch]).@Dude Markovitz – Gregory Brauninger Nov 04 '16 at 14:30
  • `case when [usr].[Udf_OverPunch](SUBSTRING(col001, 206, 11)) like '%[^0]%' then substring((SUBSTRING(col001, 206, 11)),patindex('%[^0]%',(SUBSTRING(col001, 206, 11))),len((SUBSTRING(col001, 206, 11)))) when [usr].[Udf_OverPunch](SUBSTRING(col001, 206, 11)) like '%0%' then '0' else [usr].[Udf_OverPunch](SUBSTRING(col001, 206, 11)) end as GAP_DISCOUNT_AMOUNT_THIS_PERIOD,` @Dude this is the closest I've gotten. Everything works but the function isn't doing it's job. – Gregory Brauninger Nov 04 '16 at 15:07
  • The function is supposed to turn the symbols in the output values to a specific number (which is all included in the function code). It is just not being executed. Here are some examples: '13962{', '17801H', '2969G'. It should be like '139620' '178015',29698,'. I am positive the function works its just not working with the code above. @Dudu Markovitz – Gregory Brauninger Nov 04 '16 at 15:11
  • Your coding is working to change '000013962{' into '13962{' and '0000000' into '0'. Which is great, I just need the function to work with it to replace the symbol. – Gregory Brauninger Nov 04 '16 at 15:14
  • @GregoryBrauninger, please send me your code by mail including a table definition with some sample rows (INSERT statement) – David דודו Markovitz Nov 04 '16 at 15:18
  • I used the code in my comment above that your provided me. I took out the function though. Then I put everything into a temp table and called the function. All working now. Thank's a bunch! – Gregory Brauninger Nov 04 '16 at 15:39
0

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

Community
  • 1
  • 1
gotqn
  • 42,737
  • 46
  • 157
  • 243
0

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)
KumarHarsh
  • 5,046
  • 1
  • 18
  • 22
  • `[usr].[Udf_OverPunch](case when ISNUMERIC((SUBSTRING(col001, 206, 11))=1 then cast(cast((SUBSTRING(col001, 206, 11) as bigint) as varchar(50)) else (SUBSTRING(col001, 206, 11) end) as GAP_DISCOUNT_AMOUNT_THIS_PERIOD,` Getting syntax errors here @kumarharsh – Gregory Brauninger Nov 04 '16 at 14:50