How to Select a substring in Oracle SQL

user_7674381 0
Viewed 5682

say I have a table customer with columns Firstname and lastname I would like to be able to write a query that selects all the customers which has exact lastname and the fuzzy firstname

The Substring is returning the values as expected when I run this just on the firstname column

Query

SELECT SUBSTR(firstname,1,4) from customer;

**output:**
ABCD
qwry
cvbn etc

But I am not sure how to get this work to do a match for 2 records, one of the customers having first 4 characters of firstname same as the other customer firstname.

Example:

Customer1:Firstname=ABCDXYZ Lastname=SSS

Customer2:Firstname=ABCD Lastname=SSS

The expected outcome is to be able to see these 2 records in the output

substring oracle sql
2 Answers

The below query will fetch the records who has same last names and same first four characters of first name. It uses self join. Hope the table has a primary key like id.

SELECT distinct c1.firstname, 
                c1.lastname, 
                c2.firstname, 
                c1.lastname
  FROM customer c1, customer c2
 WHERE c1.id <> c2.id 
   AND c1.lastname = c2.lastname
   AND substr(c1.firstname,1,4) = substr(c1.firstname,1,4)
user_25248300

Not totally sure I understand what you are looking to do but if you know the firstName common "prefix" you can do like this :

SELECT * from Customer WHERE firstname LIKE 'ABCD%' AND lastName = 'SSS';

user_7814290
Related