Difference between revisions of "String over columns"

Revision as of 11:28, 2 August 2012

Normally a string can be searched for across many columns using the OR function but this is not efficient as this could lead to errors while using a CONCAT function ( || for oracle, + for sqlserver) could do the same task but could get rid of any risk of errors.

schema:scott

DROP TABLE bedrooms

 CREATE TABLE bedrooms(
  name VARCHAR(20),
  floorcolor VARCHAR(20),
  ceilingcolor VARCHAR(20),
  wallcolor VARCHAR(20));
INSERT INTO bedrooms VALUES ('Jim','RED','GREEN','YELLOW');
INSERT INTO bedrooms VALUES ('Bob','YELLOW','BLUE','BLACK');
INSERT INTO bedrooms VALUES ('Allan','BLUE','PINK','BLACK');
INSERT INTO bedrooms VALUES ('George','BLUE','GREEN','OAK');

SELECT name FROM bedrooms
WHERE floorcolor = 'YELLOW'
OR ceilingcolor = 'YELLOW'
OR wallcolor = 'YELLOW'

Using OR increases the chances for careless mistakes instead CONCAT could be used to make the query more efficient.

To avoid problems make sure to add in separators and if a value can be null make sure to use it is wrapped in COALESCE or NVL for example:

COALESCE(floorcolor,' ').

SELECT name FROM bedrooms
WHERE ':' || floorcolor || ':' || ceilingcolor || ':' || wallcolor || ':'
LIKE '%:YELLOW:%'

SELECT name FROM bedrooms
WHERE ':' + floorcolor + ':' + ceilingcolor + ':' + wallcolor + ':'
LIKE '%:YELLOW:%'

SELECT name FROM bedrooms
WHERE CONCAT (':',floorcolor,':',ceilingcolor,':',wallcolor,':')
LIKE '%:YELLOW:%'

Instead of COALESCE or NVL, IFNULL can be used.

@@ Line 1: / Line 1: @@
-Search for a string across columns
+Normally a string can be searched for across many columns using the <code>OR</code> function but this is not efficient as this could lead to errors while using
+a <code>CONCAT</code> function ( || for oracle, + for sqlserver) could do the same task but could get rid of any risk of errors.
 <div class='ht'>
 <div class=params>schema:scott</div>
@@ Line 24: / Line 25: @@
 COALESCE(floorcolor,' ').
 </div>
+<source lang='sql' class='def e-oracle'>SELECT name FROM bedrooms
+WHERE ':' || floorcolor || ':' || ceilingcolor || ':' || wallcolor || ':'
+LIKE '%:YELLOW:%'</source>
+<source lang='sql' class='def e-sqlserver'>SELECT name FROM bedrooms
+WHERE ':' + floorcolor + ':' + ceilingcolor + ':' + wallcolor + ':'
+LIKE '%:YELLOW:%'</source>
 <source lang='sql' class='def'>SELECT name FROM bedrooms
 WHERE CONCAT (':',floorcolor,':',ceilingcolor,':',wallcolor,':')

Difference between revisions of "String over columns"

Revision as of 11:28, 2 August 2012

Personal tools

Namespaces

Variants

Views

Actions

Search

Reference

Toolbox

Google AdSense