SQL Puzzle | The Non Space Puzzle

In this puzzle you have to get rows where there is no space in the column a. Please check out the sample input values and sample expected output below.

Sample Input

(No column name)
|s11hp|
|s1 1hp|
||
| |
| |

Expected Output

a
|s11hp|
||

Script

Use below script to create table and insert sample data into it.

 ```-- SELECT '|' + a + '|' FROM ( SELECT 's11hp' a UNION ALL SELECT 's1 1hp' UNION ALL SELECT '' UNION ALL SELECT ' ' UNION ALL SELECT ' ' )u -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution – 1

 ``` -- SELECT '|' + a + '|' FROM ( SELECT 's11hp' a UNION ALL SELECT 's1 1hp' UNION ALL SELECT '' UNION ALL SELECT ' ' UNION ALL SELECT ' ' )u WHERE a NOT LIKE '% %' -- ```

Solution – 2

 ``` -- SELECT '|'+ a + '|' FROM ( SELECT 's11hp' a UNION ALL SELECT 's1 1hp' UNION ALL SELECT '' UNION ALL SELECT ' ' UNION ALL SELECT ' ' )u WHERE CHARINDEX(' ',a,0) = 0 -- ```

Output

```--

/*------------------------
OUTPUT
------------------------*/

--------
|s11hp|
||

(2 row(s) affected)

--
```

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com