Data Cleansing

That works too!  :)

From: Ryan Creps <bounce-ryancreps9649@tessituranetwork.com>
Sent: 7/1/2014 10:16:26 AM

There’s also a standard scalar function FS_REMOVE_NON_ALPHANUMERICS or FS_REMOVE_NON_NUMERICS, which will do this for you.

 

Sample:

select top 100

       phone,

       phone_clean = dbo.FS_REMOVE_NON_NUMERICS(phone)

from   dbo.T_PHONE

 

=>Ryan Creps

@Tessitura Network

 

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Travis Armbuster
Sent: Tuesday, July 1, 2014 11:00 AM
To: Ryan Creps
Subject: Re: [Tessitura Technical Forum] Data Cleansing

 

Give this a shot and see if this will help you out with stripping the non- numeric numbers.

/*******************************

example

**********************************/

declare @phone table(
phone Varchar(25)
)

insert into @phone values('1///(202)##600-4165Home',null)

declare @strphone varchar(max)
set @strphone = (select phone from @phone)

BEGIN
    WHILE PATINDEX('%[^0-9]%', @strphone) > 0
    BEGIN
        SET @strphone =  stuff(@strphone,PATINDEX('%[^0-9]%',@strphone),1,'')
    END
   select (select phone from @phone) as Orginal_Phone, @strphone as clean_phone
end

 

If this is the result you are looking for, you could create a function the strip all the non-numeric numbers.

example:

 

/**********************************************************************************
Function CleanUpPhones
**********************************************************************************/

CREATE Function [LFT_CleanUpPhones](@strphone VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strphone) > 0
    BEGIN
        SET @strphone =  stuff(@strphone,PATINDEX('%[^0-9]%',@strphone),1,'')
    END
  RETURN @strphone
end

GO

Grant select on [dbo].[LFT_CleanUpPhones] to impusers

go

Hope it helps.

 

Travis

 

 

From: Caspian Turner <bounce-caspianturner9368@tessituranetwork.com>
Sent: 7/1/2014 6:41:00 AM

Hi,

We're looking at integrating SMS and wondered whether anyone has any experience of data cleansing telephone numbers? Transferring from Phone 1 or 2 to a Mobile field, removing spaces and non-numerical characters.

Does anyone use a stored procedure for this or achieve it by any other means?

Thanks,
Caspian 

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

--
View this message online at http://www.tessituranetwork.com/Community/forums/p/11818/36540.aspx#36540 or reply to this message

Caspian Turner

Web Project Coordinator

Glyndebourne Productions Ltd

Tel: +44 (0)1273 812321 ext. 2244

http://glyndebourne.com

That works too!  :)

From: Ryan Creps <bounce-ryancreps9649@tessituranetwork.com>
Sent: 7/1/2014 10:16:26 AM

There’s also a standard scalar function FS_REMOVE_NON_ALPHANUMERICS or FS_REMOVE_NON_NUMERICS, which will do this for you.

 

Sample:

select top 100

       phone,

       phone_clean = dbo.FS_REMOVE_NON_NUMERICS(phone)

from   dbo.T_PHONE

 

=>Ryan Creps

@Tessitura Network

 

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Travis Armbuster
Sent: Tuesday, July 1, 2014 11:00 AM
To: Ryan Creps
Subject: Re: [Tessitura Technical Forum] Data Cleansing

 

Give this a shot and see if this will help you out with stripping the non- numeric numbers.

/*******************************

example

**********************************/

declare @phone table(
phone Varchar(25)
)

insert into @phone values('1///(202)##600-4165Home',null)

declare @strphone varchar(max)
set @strphone = (select phone from @phone)

BEGIN
    WHILE PATINDEX('%[^0-9]%', @strphone) > 0
    BEGIN
        SET @strphone =  stuff(@strphone,PATINDEX('%[^0-9]%',@strphone),1,'')
    END
   select (select phone from @phone) as Orginal_Phone, @strphone as clean_phone
end

 

If this is the result you are looking for, you could create a function the strip all the non-numeric numbers.

example:

 

/**********************************************************************************
Function CleanUpPhones
**********************************************************************************/

CREATE Function [LFT_CleanUpPhones](@strphone VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strphone) > 0
    BEGIN
        SET @strphone =  stuff(@strphone,PATINDEX('%[^0-9]%',@strphone),1,'')
    END
  RETURN @strphone
end

GO

Grant select on [dbo].[LFT_CleanUpPhones] to impusers

go

Hope it helps.

 

Travis

 

 

From: Caspian Turner <bounce-caspianturner9368@tessituranetwork.com>
Sent: 7/1/2014 6:41:00 AM

Hi,

We're looking at integrating SMS and wondered whether anyone has any experience of data cleansing telephone numbers? Transferring from Phone 1 or 2 to a Mobile field, removing spaces and non-numerical characters.

Does anyone use a stored procedure for this or achieve it by any other means?

Thanks,
Caspian 

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

--
View this message online at http://www.tessituranetwork.com/Community/forums/p/11818/36540.aspx#36540 or reply to this message

Caspian Turner

Web Project Coordinator

Glyndebourne Productions Ltd

Tel: +44 (0)1273 812321 ext. 2244

http://glyndebourne.com