Hi,
Lets say i have a column which contains a list of telephone numbers. How do i do it in SQL where i want to first use TRIM to remove spaces and the + sign and then from that value i want to only display the last 10 characters?
For Example:
Table: PhoneNumbers
Data (Column Name: Phone):
+447894561230
447894563211
7896541222
+44 7523222222
44447521236985
Result:
7894561230
7894563211
7896541222
7523222222
7521236985
Any help on this would be much appreciated, thank you.
create data script
-- Create Tables
create table SQLTeam.TempTable1(Phone varchar(100))
-- Insert data Tables
insert into SQLTeam.TempTable1 select '+447894561230'
insert into SQLTeam.TempTable1 select '447894563211'
insert into SQLTeam.TempTable1 select '7896541222'
insert into SQLTeam.TempTable1 select '+44 7523222222'
insert into SQLTeam.TempTable1 select '44447521236985'
select
'SQL'
, right(phone,10)
from
SQLTeam.TempTable1
select right(replace(replace(phone,' ',''),'+',''),10)
First, create a function to strip all non-numeric chars:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.StripNonnumericChars (
@string varchar(200)
)
RETURNS varchar(200)
WITH RETURNS NULL ON NULL INPUT
AS
--Author: Scott Pletcher.
BEGIN
DECLARE @byte int
WHILE 1 = 1
BEGIN
SET @byte = PATINDEX('%[^0-9]%', @string)
IF @byte = 0
BREAK
SET @string = STUFF(@string, @byte, 1, '')
END --WHIILE
RETURN @string
END --FUNCTION
Then, take the right-most 10 chars from the result of that function:
SELECT RIGHT(dbo.StripNonnumericChars(Phone), 10) ...
1 Like
Do you also have plans to handle this on the application side so that phone numbers are well sanitized upfront before saving to sql server?
@ScottPletcher shouldn't this function be an inline table valued function instead of a scalar function?
Here is a generic function to clean strings:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE Function [dbo].[fnCleanString] (
@inputString varchar(8000)
, @stringPattern varchar(50) = '[0-9a-zA-Z]'
)
Returns Table
With schemabinding
As
Return
With t(n)
As (
Select t.n
From (
Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
, (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
)
, iTally (n)
As (
Select Top (len(@inputString))
checksum(row_number() over(Order By @@spid))
From t t1, t t2, t t3 -- 8000 rows
)
Select v.inputString
, outputString = (Select substring(v.inputString, it.n, 1)
From iTally it
Where substring(v.inputString, it.n, 1) Like @stringPattern
For xml Path(''), Type).value('.', 'varchar(8000)')
From (Values (@inputString)) As v(inputString);
GO
This would called like this:
Select p.Phone
, pn.outputString
From dbo.Phone p
Cross Apply dbo.fnCleanString(p.Phone, '[0-9]') As pn