CODEWITHSUNDEEP
sql
adeel iqbal
adeel iqbal

Reputation: 494

SQL Query: concatenate many columns of string type in one

assume my table contains following columns

 Roll_number numeric           not null
 Subject1    varchar(40)       null
 Subject2    varchar(40)       null
 . . .
 Subject8    varchar(40)       null

i have to search a subject in all these subject1 to 8 using SQL Query. what would be the best query to search a substring in all these columns keeping in mind that any one of these can have NULL value.

i wrote 

select * 
from students_data 
where subject1="BIOLOGY" or subject2="BIOLOGY" . . . . or subject8="BIOLOGY"

but i need a simple query because in actual i have more than 20 columns in my table

The query 

select * from table1 where "BIOLOGY" in (subject1,subject2,subject3)

was helpful but what if i have to match just the substring (part of that subject) like
"BIO" in that table fields

Upvotes: 4

Views: 1084

Answers (4)

Hogan
Hogan

Reputation: 70538

This may not be faster, but I believe it is clearer.

First create a normalized view (you could just store your data this way -- which would be ideal)

Create View SubjectData AS
(
   SELECT Roll_number, 1 AS SubjectNumber, Subject1 AS Subject FROM TABLE1
   UNION ALL
   SELECT Roll_number, 2 AS SubjectNumber, Subject2 AS Subject FROM TABLE1
   UNION ALL
   SELECT Roll_number, 3 AS SubjectNumber, Subject3 AS Subject FROM TABLE1
   UNION ALL
   SELECT Roll_number, 4 AS SubjectNumber, Subject4 AS Subject FROM TABLE1
   UNION ALL
   SELECT Roll_number, 5 AS SubjectNumber, Subject5 AS Subject FROM TABLE1
   UNION ALL
   SELECT Roll_number, 6 AS SubjectNumber, Subject6 AS Subject FROM TABLE1
   UNION ALL
   SELECT Roll_number, 7 AS SubjectNumber, Subject7 AS Subject FROM TABLE1
   UNION ALL
   SELECT Roll_number, 8 AS SubjectNumber, Subject8 AS Subject FROM TABLE1
)

Now the select is simple:

SELECT Roll_number, SubjectNumber from SubjectData where CONTAINS(Subject,'Bio')

N.B. Note the use of CONTAINS, which should be faster however you do it.

I did not test the above queries, they may have typos.

Upvotes: 1

Fahim Parkar
Fahim Parkar

Reputation: 31647

How about this?

SELECT * 
FROM students_data
WHERE subject1 + ' ' + subject2 + ' ' + ..... + subject8 LIKE '%BIOLOGY%';

Update 1

For NULL values, use below.

SELECT * 
FROM students_data
WHERE ISNULL(subject1,'') + ' ' + ISNULL(subject2,'')
+ ' ' + ..... + ISNULL(subject8,'') LIKE '%BIOLOGY%';

For using BIO, you can use LIKE '%BIO%';

Upvotes: 1

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10073

In such case hw about this,

SELECT * 
FROM students_data
WHERE 
nvl(subject1,'') + ' ' + nvl(subject2,'') + ' ' + ..... + nvl(subject8,'') 
LIKE '%BIO%';

If your db doesnt support then go for ifnull() or decode()

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

You can use the IN predicate like so:

SELECT * 
FROM students_data 
WHERE 'BIOLOGY' IN (subject1, subject2, . . . , subject8);

Upvotes: 5

Related Questions