How to Select a substring in Oracle SQL

Question

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

what is you question ?

Ravi
– Ravi

2017-10-03 17:29:40 +00:00
Commented Oct 3, 2017 at 17:29 — Ravi
– Ravi, Commented Oct 3, 2017 at 17:29

Valli · Accepted Answer · 2017-10-03 17:20:55Z

1

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)

answered Oct 3, 2017 at 17:20

Valli

1,4601 gold badge8 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user0726 Over a year ago

Perfect! small correction substr(c1.firstname,1,4) = substr(c2.firstname,1,4)

Valli Over a year ago

oh ya thats a typo

Dessma · Accepted Answer · 2017-10-03 17:19:50Z

0

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';

answered Oct 3, 2017 at 17:19

Dessma

6093 silver badges11 bronze badges

1 Comment

user0726 Over a year ago

Basically I am trying to find matches based on fuzzy firstname(2 customers records who has match on first 4 characters of the firstname) and exact lastname.I am planning to extract a potential match report from customer table.Having said that I don't have information of customers who has the same last name and firstname fuzzy.That is what I am trying to achieve by running this query.

Collectives™ on Stack Overflow

How to Select a substring in Oracle SQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related