BODS - SQL Transform case statement with '%[0-9]%'

[former_member1291121]

I have a case statement in SQL

Case when Fieldname like '%[0-9]%' then NULL else Fieldname end as Field

This works fine when the SQL is run by itself, but when embedded on SQL Transform and utilized on a Dataflow, it skips the whole statement and renders Fieldname for Field.

Things I tried

1. I tried to use Match_regex in the Query transform. But since the field does not have a pattern and a number could be any where, that did not work.

2. Tried ifthenelse on Query transform, no luck.

I have looked up all the Q&A and forums could not find a solution, please suggest. Thank you.

[lenastodal]

Hi and welcome to the SAP Community!

Thank you for visiting SAP Community to get answers to your questions. Since you're asking a question here for the first time, I recommend that you familiarize yourself with https://community.sap.com/resources/questions-and-answers (if you haven't already), as it provides tips for preparing questions that draw responses from our members. For example, you can outline what steps you took to find answers (and why they weren't helpful) and share screenshots of what you've seen/done. The more details you provide, the more likely it is that members will be able to assist you.
Should you wish, you can still edit your question.
Finally, if you're hoping to connect with readers, please consider adding a picture to your profile as it encourages readers to respond to you. Here's how you do it: https://www.youtube.com/watch?v=F5JdUbyjfMA&list=PLpQebylHrdh5s3gwy-h6RtymfDpoz3vDS.

Best,
Lena
SAP Community Moderator

[werner_daehn]

We need to distinct between functions in BODS and in the source database.

When using the SQL transform, the string is passed as-is to the database. Well, almost as-is, it does have the option to use parameter substitution within [] and {} brackets. And if I am right, this is your problem. You need to escape the [].

select ..., Case when Fieldname like '%\[0-9\]%' then NULL else Fieldname end as Field ....

Having said that, I would ask myself if a SQL Transform is the wisest option and rather prefer Julian's approach of BODS functions and Query transform. When using an ifthenelse() function, the second parameter - NULL in your case - determines the return type. Not a good idea.

So I would use:

ifthenelse(match_simple(FieldName,'[0-9]')=1, FieldName, null)

This should return either the value in the column FieldName if it is a string, its length is one and the char is a single digit number.

Given your initial query, probably a

ifthenelse(match_simple(FieldName,'*[0-9]*')=1, FieldName, null)

is what you are actually looking for.

Note: I haven't tested it.

[former_member1291121]

Thank you for analyzing my issue, you are right [0-9] looking for the range, or checking for numeric values on the field is what is not working. I have tried all that you posted above and unfortunately nothing worked.

So I came up with a user defined function on SQL to check if there are any numeric values on the string. It will slow down my query a bit but that is the closest I could get to, after several hours of trying different DI functions, even tried to use Index(). For everything [0-9] was problem. I will wait to see if i get any other inputs to try before i close this. Thank you again.

[werner_daehn]

sgilla14 What I would do next is create a job with a single script object and try out the various versions with fixed input text values, e.g.

print(match_simple('1234','*[0-9]*'));

This will allow you to analyze the individual components and figure out what the root cause actually is.

[Julian_Riegel]

Hi there,

have you tried using match_simple?

https://help.sap.com/viewer/8092b085a68941f6aaa6708685a62b0d/4.2.11/en-US/5765634a6d6d1014b3fc9283b0...

match_simple(FieldName,'$') = 0

You could use it in a query transform as in:

Ifthenelse(match_simple(FieldName,'$') = 0, NULL, FieldName)

Regards,

Julian

[former_member1291121]

Ifthenelse(match_simple(FieldName,[0-9])=0,NULL, FieldName) I tried this, but no luck. I think i have my pattern string wrong. Thank you for quick response.

[Julian_Riegel]

Hi there,

I have done some quick tests for you - the above was just out of my head and I did not test it either.

I forgot the single quotes in above Query:

Ifthenelse(match_simple(FieldName,'$') = 0, NULL, FieldName)

Also, if you want to turn it around like wdaehn suggested, you can use:

Ifthenelse(match_simple(FieldName,'$') = 1, FieldName, NULL)

The dollar sign represents any alphabetic character, including non-English letters, zero or more times.

Also you can use what wdaehn suggested below, but you would need to put it like this:

ifthenelse(match_simple(FieldName,'*[0..9]*')= 0, FieldName, null)

regards,

Julian

[werner_daehn]

you are correct of course, [0-9] is wrong, must be [0..9].

Thanks Julian.