CODEWITHSUNDEEP
sql-server-2008
user2432565
user2432565

Reputation: 1

Dealing with mobile numbers - SQL Server 2008

I'm using SQL Server 2008. I am trying to get values in Column A based on the mobile numbers in Column B. The mobile numbers can be in different formats like this: 

 1. 44 752 111 1234
 2. 07521111234
 3. 07521111234
 4. 447521111234
 5. 7521111234
 6. 752 111 1234 etc...etc.

I need the syntax for extracting data in column A based on the mobile number, but only with the 10 digits starting from 75 as the search criteria. The prefixes (44,0) and the spaces in between must be disregarded.

Also note that in the 10 digits I am referring to the numbers start with all combinations, I think only 7 remains constant. i.e it can be 077 or 075 or 078 or 75 or 78 and so on..

Can you please help ? Thanks.

Upvotes: 0

Views: 100

Answers (3)

bendataclear
bendataclear

Reputation: 3850

If you are dealing with fixed length no's (eg UK mobile numbers) I've found the best approach to be:

RIGHT(REPLACE([MyColumn],' ',''),10)

SQL Fiddle

Upvotes: 0

Yaugen Vlasau
Yaugen Vlasau

Reputation: 2218

DECLARE @Tels AS TABLE (no varchar(20))

INSERT INTO @Tels
VALUES
('44 752 111 1234'),
('07521111234'),
('07521111234'),
('447521111234'),
('7521111234'),
('752 111 1234')

SELECT SUBSTRING(REPLACE(no, ' ', ''), 3, 10) from @Tels where no like '75%'

regarding your second question...

SELECT SUBSTRING(REPLACE(no, ' ', ''), 3, 10) from @Tels where no like '7[0-9]%'
UNION
SELECT SUBSTRING(REPLACE(no, ' ', ''), 4, 10) from @Tels where no like '07[0-9]%'

Upvotes: 0

John Woo
John Woo

Reputation: 263733

You can use CHARINDEX to help you solve your problem,

SELECT  ColA, 
        SUBSTRING(ColA, CHARINDEX('75', ColA), LEN(ColA) - CHARINDEX('75', ColA) + 1)
FROM    TableName

OR

SELECT  ColA, 
        RIGHT(ColA, LEN(ColA) - CHARINDEX('75', ColA) + 1) ColB
FROM    TableName

OUTPUT of BOTH query,

╔═════════════════╦══════════════╗
║      COLA       ║     COLB     ║
╠═════════════════╬══════════════╣
║ 44 752 111 1234 ║ 752 111 1234 ║
║ 07521111234     ║ 7521111234   ║
║ 07521111234     ║ 7521111234   ║
║ 447521111234    ║ 7521111234   ║
║ 7521111234      ║ 7521111234   ║
║ 752 111 1234    ║ 752 111 1234 ║
╚═════════════════╩══════════════╝

Upvotes: 1

Related Questions