February 16, 2009 at 6:21 am
I have a telephone number field that I am importing from a MySql database that can have varying forms. The phone number is coming from a String formatted field and can be in several different formats. I would like to store the tel number in a smallint field, so that I am just keeping the numbers.
The problem that I have is that the international or national code number can be seperated from the local number by "/" or "-" (for example) - whats the best way to pull these extra chrs out using a derived column transformation... or is there an easier way I'm missing?
Thx
February 16, 2009 at 6:47 am
Just adapted a function I have to strip different characters, this won't give you a small int, but do you really need this to be a small int. Where I come form many phone numbers start with a leading zero....
/* =============================================================================
-- Author:Carolyn Richardson
-- Create date: 16 February 2008
-- Description:Strip Non alpha characters out of string
-- Example:-
select dbo.fn_StripNonNumeric('0151/01515-5666666666') --gives 0151015155666666666
-- Notes:Used for Phone Numbers
ASCII characters:-
48-57 = 0-9
-- =============================================================================*/
CREATE FUNCTION [dbo].[fn_StripNonNumeric]
(
@OrigString VARCHAR(255)
)
RETURNS VARCHAR(255)
AS BEGIN
DECLARE @NewString VARCHAR(255),
@Space INT,
@Len INT,
@Ctr AS INT,
@Ctr2 AS INT,
@Char AS VARCHAR(1)
SET @NewString = @OrigString
SET @Len = ( SELECT LEN(@NewString)
)
SELECT @Ctr2 = 1,
@Ctr = 1
WHILE @Ctr <= @Len
BEGIN
SELECT @Char = SUBSTRING(@NewString, @Ctr, 1)
IF ASCII(@Char) BETWEEN 0 AND 47
OR ASCII(@Char) BETWEEN 58 AND 127
BEGIN
SET @NewString = ( SELECT REPLACE(@NewString, @Char, '')
)
SELECT @Ctr2 = @Ctr2 + 1
END
SELECT @Ctr = @Ctr + 1
CONTINUE
END
RETURN @NewString
END
February 16, 2009 at 8:33 am
...You can do a REPLACE of such characters with an empty string.
REPLACE( REPLACE(TelephoneNo, "-",""), "/", "" )
--Ramesh
February 16, 2009 at 12:36 pm
Thx - obviously for the REPLACE function to work, you have to know all the strange chrs that users may have entered - not possible to cover all eventualities.
Carolyn - I've never created a fn like this in SSIS before, and am not completely sure I understand all the code. Could you possibly give me a break down of where I would use this and how it works?
Thx
February 16, 2009 at 3:17 pm
I can't see what you are attempting, but you can bring the data into you destination table then do an update on the field as a SQL task ie:-
update table
set PhoneNo = dbo.fn_StripNonNumeric(PhoneNo)
Assumes the function is stored in the database you are pointing to.
February 17, 2009 at 12:32 am
Carolyn Richardson (2/16/2009)
Just adapted a function I have to strip different characters, this won't give you a small int, but do you really need this to be a small int. Where I come form many phone numbers start with a leading zero....
I think this function does not strip all the non-numeric values from a string, see these examples
SELECTdbo.[fn_StripNonNumeric]('0ABC-789-784X1'),
dbo.[fn_StripNonNumeric]('0asdfasf#'),
dbo.[fn_StripNonNumeric]('0987897-x-189')
...But, this function works well on all types of strings.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnDigitsOnly]
(
@TextToExtract VARCHAR(50)
)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @NonNumericPos TINYINT
SET @NonNumericPos = PATINDEX( '%[^0-9]%', @TextToExtract )
IF ( @NonNumericPos > 0 )
BEGIN
WHILE ( @NonNumericPos > 0 )
BEGIN
SELECT@TextToExtract = STUFF( @TextToExtract, @NonNumericPos, 1, '' ),
@NonNumericPos = PATINDEX( '%[^0-9]%', @TextToExtract )
END
END
RETURN( @TextToExtract )
END
--Ramesh
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply