Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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