CODEWITHSUNDEEP
phpmysqlsqlsql-like
Developer
Developer

Reputation: 847

sub string with escape characters and like wildcards

I have a string Test_x_x_T1 and i want to replace last three characters i.e _T1 if the string contain these three characters otherwise return null. the last characters can be _T1, _T2,_T3

but i am getting always null

select  
case when 'Test_x_x_T1' like '[_]%T_'
then substring(trim('Test_x_x_T1'),1,length(trim('Test_x_x_T1'))-2)
else null end test

Upvotes: 0

Views: 89

Answers (4)

crazycloud
crazycloud

Reputation: 179

Try this

select case when 'Test_x_x_T1' like '%\_T_' escape '\' then substr(trim('Test_x_x_T1'),1,length(trim('Test_x_x_T1'))-3) else null end from dual

If you are matching _ before T, you should escape it because _ has predefined meaning for like operator.

If _ is not escaped even this string Test_x_xT1 will return not null result.

Upvotes: 0

Ravinder Reddy
Ravinder Reddy

Reputation: 24022

Underscore is a wild card for single character search.
If your data contains an underscore ( _ ), and want to use it as part of your like pattern, then escape it.

Example: 

mysql> select * from (
    ->   select 1 rnum, 'Test_x_x_T1' t
    ->   union all
    ->   select 2, 'Test_x_xT1_'
    ->   union all
    ->   select 3, 'Test_x_xT1'
    ->   union all
    ->   select 4, 'Test_x_x_T1_'
    -> ) tst
    -> where t like '%\_T_';
+------+-------------+
| rnum | t           |
+------+-------------+
|    1 | Test_x_x_T1 |
+------+-------------+
1 row in set (0.00 sec)

Upvotes: 2

PeterRing
PeterRing

Reputation: 1797

I think you over complicate it. 

 select  
    case when 'Test_x_x_T1' like '%_T_'
    then substring(trim('Test_x_x_T1'),1,length(trim('Test_x_x_T1'))-3)
    else null end test

Upvotes: 0

bosnjak
bosnjak

Reputation: 8624

You should try this:

case when 'Test_x_x_T1' like '%[_]%T_'

You need to match the first part of the string, before the '_'.

Upvotes: 0

Related Questions