CODEWITHSUNDEEP
sql
C.cena
C.cena

Reputation: 45

Replace LIKE by SUBSTR

I tried to select the name of students that end with 'a'. I wrote this code: 

Select name form students where name like '%a' ;

How can I get the same results using SUBSTR?

Upvotes: 1

Views: 955

Answers (4)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

I actually think using RIGHT() would make the most sense here:

SELECT name
FROM students
WHERE RIGHT(name, 1) = 'a'

The above query would work on MySQL, SQL Server, and Postgres, but not Oracle, where you would have to use SUBSTR():

SELECT name
FROM students
WHERE SUBSTR(name , -1) = 'a'

Upvotes: 5

DecoderReloaded
DecoderReloaded

Reputation: 514

You can use :

Select name from students where SUBSTR(name, -1, 1) = 'a' ;

Upvotes: 1

marcothesane
marcothesane

Reputation: 6749

Not all platforms accept negative start integers or length integers for SUBSTR()

Can you try if your DBMS supports the RIGHT() string function?

Works like this:

SQL>SELECT RIGHT('abcd',1) AS rightmost_char;
rightmost_char
--------------
d

Happy playing ...

Marco

Upvotes: 1

Matt
Matt

Reputation: 15061

Using SUBSTR().

SELECT name 
FROM students 
WHERE SUBSTR(name , -1) = 'a'

Upvotes: 0

Related Questions