cancel
Showing results for 
Search instead for 
Did you mean: 

finding the charecter in the object

Former Member
0 Kudos

hi i have one query

suppose i have a data of two field

Name State

tom gujrat

billy rajasthan

jeny maharastra

now i want to check in "Name" field ,which are the record contains the letter 'y'

tht means after processing it shoud show output with word 'y' i.e.

Name State

billy rajasthan

jeny maharastra

thnks

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

In answer to the original question the where clause should look like the below

index( Query.Column,'Y',1) > 0

This will look for the letter Y within the Column specified and if found return its position therefore if the value is 0 the string was not found.

Former Member
0 Kudos

Place a query transform next to your source table (say TABLEA)

In the Where clause of Query transform, use the following:

substr(TABLEA.Name,length(TABLEA.Name),1) = 'y'

Map the required fields to output Schema. You will get the filtered data.

Regards,

Suneer

Former Member
0 Kudos

i did what u said...but its showing this error

[Query:Query]

Invalid mapping expression for column <Query.STATE>. Additional information: <Comparison Expression < substr( NEW_TT111.STATE ,length( NEW_TT111.STATE ) , 1 ) = 'b'

> is not allowed. (BODI-1111320)>. (BODI-1111081)

Former Member
0 Kudos

You should not do it in the mapping tab. This has to be used in the Where tab inside the Query transform.

Regards,

Suneer

Former Member
0 Kudos

i did the same now it is executing successfully but output table contains zero records

Former Member
0 Kudos

hii suneer,

is there any posibility doing it by match_pattern ???

Former Member
0 Kudos

actully there is a misundstanding happnd,

the charecter 'y' is not necessary to be placed at the last of the record value

like if the 'name' contains the value 'rubyram' then tht record also shud come in the filter list

thnks

Former Member
0 Kudos

Yes as you said, match pattern is the option. The requirement was not clear in your earlier post. This is how it can be done.

Suppose your Name field comes from TABLEA. Connect TABLEA to a Query transform

Use the following in the Where clause of your Query transform.

match_pattern(TABLEA.Name,'y') = 1

Map the required fields to the Output Schema of the Query transform. The records will be filtered as per your requirement.

Please also note that this is case sensitive. match_pattern(upper(TABLEA.Name),'Y') = 1 will give you records into output even if the source field contains Y (upper case).

Regards,

Suneer

Former Member
0 Kudos

Hi Guys,

Is it possible to use multiple characters in the pattern? What if I want to get the rows that have either one of these characters or some of them or maybe all of them: ('!','@','$').

It doesn´t matter it they are one next to the other or separate.

The thing is that I am receiving in a text file some wierd characters that I need to identify so the source can correct them and send them again.

This is an ETL process.

All the best.

Luis.

Former Member
0 Kudos

Hi Luis,

In the Where tab of a Query transform, you can use this.

match_pattern(Table.FieldName,'[\!@$]') = 1

This will filter only those records with special characters mentioned in your list.

Please ignore the strikethrough.

Regards,

Suneer.

Former Member
0 Kudos

Hi Suneer,

Thanks for your answer.

I just tried your code but it didnt work as expected.

match_pattern(f1||f2||...fn, '\u00B5u201A¡Ö¢u2022£é¤¥') = 1

I believe your code is considering to find all those chars together.

Instead I had to write it like this to make it work:

match_pattern(f1||f2||...fn, 'µ') = 1

or

match_pattern(f1||f2||...fn, '¡') = 1

or

....

match_pattern(f1||f2||...fn, '¥') = 1

I didnt like it though.

Thanks.

Luis.

Former Member
0 Kudos

Hi Luis,

Please try this. This has to work.


match_pattern(Table_Name.Feild_Name,'*[µu201A¡Ö¢u2022£é¤¥]*') = 1

Please enclose the special characters inside a square bracket. The asterix symbols should be outside the braces. I tried to post likewise, but was taken as a URL by the text help

Regards,

Suneer.