String over columns - Revision history

Connor at 15:01, 2 August 2012

2012-08-02T15:01:17Z

Connor at 10:29, 2 August 2012

2012-08-02T10:29:36Z

Connor at 10:28, 2 August 2012

2012-08-02T10:28:20Z

Connor at 13:38, 30 July 2012

2012-07-30T13:38:33Z

Connor at 11:57, 30 July 2012

2012-07-30T11:57:25Z

Connor at 11:56, 30 July 2012

2012-07-30T11:56:11Z

Connor at 10:56, 30 July 2012

2012-07-30T10:56:51Z

Connor at 10:56, 30 July 2012

2012-07-30T10:56:37Z

Connor at 10:33, 30 July 2012

2012-07-30T10:33:04Z

Connor at 10:26, 30 July 2012

2012-07-30T10:26:59Z

@@ Line 16: / Line 16: @@
 <div>
   SELECT name FROM bedrooms
- WHERE floorcolor = 'YELLOW'
+  WHERE floorcolor = 'YELLOW'
- OR ceilingcolor = 'YELLOW'
+     OR ceilingcolor = 'YELLOW'
- OR wallcolor = 'YELLOW'
+     OR wallcolor = 'YELLOW'
 <p>Using <code>OR</code> increases the chances for careless mistakes instead
 <code>CONCAT</code> could be used to make the query more efficient.</p>
@@ Line 25: / Line 25: @@
 COALESCE(floorcolor,' ').
 </div>
-<source lang='sql' class='def e-oracle'>SELECT name FROM bedrooms
+<source lang='sql' class='def e-oracle'>
-WHERE ':' || floorcolor || ':' || ceilingcolor || ':' || wallcolor || ':'
+SELECT name FROM bedrooms
-LIKE '%:YELLOW:%'</source>
+ WHERE ':' || floorcolor || ':' || ceilingcolor || ':' || wallcolor || ':'
-<source lang='sql' class='def e-sqlserver'>SELECT name FROM bedrooms
+  LIKE '%:YELLOW:%'</source>
-WHERE ':' + floorcolor + ':' + ceilingcolor + ':' + wallcolor + ':'
+<source lang='sql' class='def e-sqlserver'>
-LIKE '%:YELLOW:%'</source>
+SELECT name FROM bedrooms
-<source lang='sql' class='def'>SELECT name FROM bedrooms
+ WHERE ':' + floorcolor + ':' + ceilingcolor + ':' + wallcolor + ':'
-WHERE CONCAT (':',floorcolor,':',ceilingcolor,':',wallcolor,':')
+  LIKE '%:YELLOW:%'</source>
-LIKE '%:YELLOW:%'</source>
+<source lang='sql' class='def'>
 <div class="ecomm e-mysql" style="display: none">Instead of <code>COALESCE</code> or <code>NVL</code>, <code>IFNULL</code> can be used.</div>
 <div class="ecomm e-sqlserver" style="display: none">Instead of <code>COALESCE</code> or <code>NVL</code>, <code>IFNULL</code> can be used.</div>
 </div>
 {{Hacks Ref}}

@@ Line 1: / Line 1: @@
 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.
+a <code>CONCAT</code> function ( || for oracle, + for sqlserver) could do the same task but can get rid of the risk of errors
 <div class='ht'>
 <div class=params>schema:scott</div>

@@ 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
 <div class='ht'>
 <div class=params>schema:scott</div>
@@ Line 24: / Line 25: @@
 COALESCE(floorcolor,' ').
 </div>
 <source lang='sql' class='def'>SELECT name FROM bedrooms
 WHERE CONCAT (':',floorcolor,':',ceilingcolor,':',wallcolor,':')

@@ Line 18: / Line 18: @@
   OR ceilingcolor = 'YELLOW'
   OR wallcolor = 'YELLOW'
-<p>Using OR increases the chances for careless mistakes instead
+<p>Using <code>OR</code> increases the chances for careless mistakes instead
-CONCAT could be used to make the query more efficient.</p>
+<code>CONCAT</code> could be used to make the query more efficient.</p>
-<p>To avoid problems make sure to add in separators and if a value cna be null
+<p>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:</p>
+make sure to use it is wrapped in <code>COALESCE</code> or <code>NVL</code> for example:</p>
 COALESCE(floorcolor,' ').
 </div>
@@ Line 27: / Line 27: @@
 WHERE CONCAT (':',floorcolor,':',ceilingcolor,':',wallcolor,':')
 LIKE '%:YELLOW:%'</source>
-<div class="ecomm e-mysql" style="display: none">Instead of COALESCE or NVL, IFNULL can be used.</div>
+<div class="ecomm e-mysql" style="display: none">Instead of <code>COALESCE</code> or <code>NVL</code>, <code>IFNULL</code> can be used.</div>
-<div class="ecomm e-sqlsever" style="display: none">Instead of COALESCE or NVL, IFNULL can be used.</div>
+<div class="ecomm e-sqlserver" style="display: none">Instead of <code>COALESCE</code> or <code>NVL</code>, <code>IFNULL</code> can be used.</div>
 </div>
 {{Hacks Ref}}

@@ Line 22: / Line 22: @@
 <p>To avoid problems make sure to add in separators and if a value cna be null
 make sure to use it is wrapped in COALESCE OR NVL for example:</p>
-COALESCE(floorcolor,'').
+COALESCE(floorcolor,' ').
 </div>
 <source lang='sql' class='def'>SELECT name FROM bedrooms

@@ Line 6: / Line 6: @@
    name VARCHAR(20),
    floorcolor VARCHAR(20),
-   ceilingcolor VRACHAR(20),
+   ceilingcolor VARCHAR(20),
-   wallcolor VARCHAR(20);
+   wallcolor VARCHAR(20));
 INSERT INTO bedrooms VALUES ('Jim','RED','GREEN','YELLOW');
 INSERT INTO bedrooms VALUES ('Bob','YELLOW','BLUE','BLACK');