SQL - Trim and then LEFT

Other SQL Server Topics
1

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.

2
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

image

3

select right(replace(replace(phone,' ',''),'+',''),10)

4

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
5

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?

6

@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