SQL Puzzle | Search Phone Number Puzzle (Escape Multiple Characters)

This puzzle is really simple. You have to accept a parameter and search that parameter value in a column called PhoneNumber. The catch here is that the column PhoneNumber has some characters that you have escape. Some of them are -,+,(,),Space…

Please check out the sample input and the expected output below-

Sample Input

PhoneNumber
+91-9873686544
9873686544
+1 (509) 919-7401
+91-8860599569
+1 (509) 919-7401
9209881113
+91-9209881113
+1 (509) 919-7401
+1 (519) 919-7441

Expected Output

PhoneNumber
+1 (509) 919-7401
+1 (509) 919-7401
+1 (509) 919-7401

Rules/Restrictions

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

Script | use below script to create table & insert some sample data

 ``` -- CREATE TABLE TestHyphen ( PhoneNumber NVARCHAR(100) ) GO INSERT INTO TestHyphen VALUES (N'+91-9873686544'), (N'9873686544'), (N'+1 (509) 919-7401'), (N'+91-8860599569'), (N'+1 (509) 919-7401'), (N'9209881113'), (N'+91-9209881113'), (N'+1 (509) 919-7401'), (N'+1 (519) 919-7441') GO -- ```

Solution 1

 ``` -- DECLARE @ AS VARCHAR(100) = '5099' SELECT PhoneNumber FROM TestHyphen x CROSS APPLY ( SELECT DISTINCT ( SELECT '' + y.chr FROM ( SELECT Number, CASE WHEN ASCII(SUBSTRING(PhoneNumber,Number,1)) BETWEEN 48 AND 57 THEN 1 ELSE 2 END v , SUBSTRING(PhoneNumber,Number,1) chr FROM ( SELECT DISTINCT Number FROM MASTER..SPT_VALUES WHERE NUMBER > 0 AND NUMBER <= DATALENGTH(PhoneNumber) )v) y WHERE ( 1 = y.v ) FOR XML PATH('') ) AS IntValues FROM (SELECT Number, CASE WHEN ASCII(SUBSTRING(PhoneNumber,Number,1)) BETWEEN 48 AND 57 THEN 1 ELSE 2 END v , SUBSTRING(PhoneNumber,Number,1) chr FROM ( SELECT DISTINCT Number FROM MASTER..SPT_VALUES WHERE NUMBER > 0 AND NUMBER <= DATALENGTH(PhoneNumber) )v) x )r WHERE IntValues LIKE '%' + @ + '%' -- ```

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