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

[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.