CODEWITHSUNDEEP
oracle-database
user3304494
user3304494

Reputation: 1

Selecting a value based on matching the substring in where condition

I have a table called country which has column as shown below

countryname  statename 

 USA         TEXAS
INDIA        DELHI,MAHARASHTRA
ENGLAND      KENT

I want to select a country name by passing the statename as a parameter in a Stored Function. But for the case, India , the statename has multiple values separated by ','

How do you put where condition in select so that i get countryname as INDIA , but the parameter passed is either 'DELHI' or 'MAHARASHTRA' not both

Thanks in Advance

Upvotes: 0

Views: 63

Answers (3)

PRK
PRK

Reputation: 73

Given the fact that you have decided to use RDBMS, you should normalise the data.

Suggested design is to have 

COUNTRY     STATE
INDIA       DELHI
INDIA       MAHARASHTRA
USA         TEXAS

When you data grows large, assuming you keep searching by providing name of the state, you would index STATE attribute, and suggested design would help you.

Upvotes: 0

San
San

Reputation: 4538

Use this query and let us know the result

WITH country(countryname,statename ) AS (
SELECT 'USA','TEXAS' FROM dual UNION ALL
SELECT 'INDIA','DELHI,MAHARASHTRA' FROM dual UNION ALL
SELECT 'ENGLAND','KENT' FROM dual)
----------
--- End of data preparation
----------
SELECT countryname 
from (SELECT countryname, regexp_substr(statename, '[^,]+', 1, level) statename
        FROM country
       CONNECT BY regexp_substr(statename, '[^,]+', 1, LEVEL) IS NOT NULL
         AND PRIOR statename = statename
         AND PRIOR sys_guid() IS NOT NULL)
where statename = 'MAHARASHTRA';

Output

| COUNTRYNAME |
|-------------|
|       INDIA |

Upvotes: 0

ChaseMedallion
ChaseMedallion

Reputation: 21764

Why not use LIKE?

For example, you do:

DECLARE @stateName VARCHAR(MAX) = 'MAHARASHTRA';

SELECT countryName
FROM table
WHERE (',' || stateName || ',') LIKE '%,' || @stateName || ',%'

Upvotes: 2

Related Questions