Strip Non-Numeric Characters from Varchar
-
I have a varchar field with numeric and non-numeric data. I'd like to strip out any non-numeric character with a stored procedure. Thanks.
-
I would look at using ISNUMERIC() possibly. It returns a 1 or a 0 on its evaluation. That assumes you're OK with data like A123BC acting as non-numeric.
-
This is an approach I took with Invoice Numbers. It might help. The table name is AP and the two fields are RowID and InvoiceNum. It is updating the CleanInvoiceNum column of AP table with InvoiceNum where only numbers will exist. If there are no numerics, I default to zero.
SET NOCOUNT ON
DECLARE @CurrentID int,
@MaxID int,
@OriginInvoiceNumber varchar(25),
@TempInvoiceNumber varchar(1),
@InvoiceNumber varchar(25),
@CurrentPosition int,
@Length int -- Table
SELECT @CurrentID = (SELECT MIN( RowID) FROM AP),
@MaxID = (SELECT MAX( RowID) FROM AP)
WHILE @CurrentID <= @MaxID
BEGIN
SELECT @InvoiceNumber = ' '
SELECT @OriginInvoiceNumber = ' ' -- Table
SELECT @OriginInvoiceNumber = ISNULL( (SELECT InvoiceNum FROM AP WHERE RowID = @CurrentID), '0')
IF @OriginInvoiceNumber <> '0'
BEGIN
SELECT @CurrentPosition = 1
SELECT @Length = (SELECT LEN( RTRIM( LTRIM( @OriginInvoiceNumber))))
WHILE @CurrentPosition <= @Length
BEGIN
SELECT @TempInvoiceNumber = (SELECT SUBSTRING( @OriginInvoiceNumber, @CurrentPosition, 1))
IF @TempInvoiceNumber BETWEEN CHAR(48) AND char(57)
BEGIN
SELECT @InvoiceNumber = (SELECT @InvoiceNumber + @TempInvoiceNumber)
END
SELECT @CurrentPosition = @CurrentPosition + 1
END
END
IF ISNUMERIC( @InvoiceNumber) = 0
BEGIN
SELECT @InvoiceNumber = '0'
END
IF ISNUMERIC( @InvoiceNumber) = 1
BEGIN -- Table
BEGIN TRANSACTION CleanInvoiceNum
UPDATE AP SET
CleanInvoiceNum = CONVERT( numeric(38,0), RTRIM( LTRIM( @InvoiceNumber)))
WHERE RowID = @CurrentID
COMMIT TRANSACTION CleanInvoiceNum
END
-- Table
SELECT @CurrentID = (SELECT MIN( RowID) FROM AP WHERE RowID > @CurrentID)
END
I wasn't born stupid - I had to study.
-
Create a UDF similar to Farrell's SP.
--Usage
SELECT column1, dbo.udf_StripNonNumeric(column2) FROM MyTable
CREATE FUNCTION dbo.udf_StripNonNumeric (@str varchar(100) )
RETURNS varchar(100) AS
BEGIN
DECLARE @retStr varchar(100)
DECLARE @iLen int, @idx int
DECLARE @C char(1)
IF @STR IS NULL
BEGIN
RETURN NULL
END
SET @retStr = ''
SET @iLen = LEN(@str)
SET @idx = 1
WHILE @idx <= @iLen
BEGIN
SET @C = SUBSTRING(@str, @idx, 1)
IF ISNUMERIC(@c)=1
BEGIN
SET @retStr = @retStr + @C
END
SET @idx = @idx+1
END
RETURN @retStr
END
-
CREATE FUNCTION dbo.RemoveChars(@Input varchar(1000))
RETURNS VARCHAR(100)
BEGIN
DECLARE @pos INT
SET @Pos = PATINDEX('%[^0-9]%',@Input)
WHILE @Pos > 0
BEGIN
SET @Input = STUFF(@Input,@pos,1,'')
SET @Pos = PATINDEX('%[^0-9]%',@Input)
END
RETURN @Input
END
GO
SELECT 'a1sdsad124325143gffdfd4dgsf',dbo.RemoveChars('a1sdsad124325143gffdfd4dgsf')
DROP FUNCTION dbo.RemoveChars
would be another way.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Very elegant.
Thanks Frank (I figured someone would have a better approach than my bull dozer method)
I wasn't born stupid - I had to study.
-
Proud to give credit for the UDF to Jonathan van Houtte!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I'll take this opportunity to promote my own generic, pattern-based replacement UDF for your consumption:
http://sqljunkies.com/WebLog/amachanic/articles/PatternReplace.aspx
Enjoy!
--
Adam Machanic
whoisactive -
Cute! But what a shameless plug this is, Adam!
What do you think of this slight modification?
CREATE FUNCTION dbo.PatternReplace
(
@InputString VARCHAR(4000),
@Pattern VARCHAR(100),
@ReplaceText VARCHAR(4000)
)
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @Result VARCHAR(4000)
-- First character in a match
DECLARE @First INT
-- Next character to start search on
DECLARE @Next INT SET @Next = 1
-- Length of the total string
DECLARE @Len INT SET @Len = LEN(@InputString)
-- End of a pattern
DECLARE @EndPattern INT
WHILE @Next <= @Len
BEGIN
SET @First = PATINDEX('%' + @Pattern + '%', SUBSTRING(@InputString, @Next, @Len))
IF @First = 0 --no match - return
BEGIN
SET @Result = ISNULL(@Result + SUBSTRING(@InputString, @Next, @Len),'')
BREAK
END
ELSE
BEGIN
-- Concatenate characters before the match to the result
SET @Result = ISNULL(@Result + SUBSTRING(@InputString, @Next, @First - 1),'')
SET @Next = @Next + @First - 1
SET @EndPattern = 1
-- Find start of end pattern range
WHILE PATINDEX(@Pattern, SUBSTRING(@InputString, @Next, @EndPattern)) = 0
SET @EndPattern = @EndPattern + 1
-- Find end of pattern range
WHILE PATINDEX(@Pattern, SUBSTRING(@InputString, @Next, @EndPattern)) > 0
AND @Len >= (@Next + @EndPattern - 1)
SET @EndPattern = @EndPattern + 1
--Either at the end of the pattern or @Next + @EndPattern = @Len
SET @Result = ISNULL(@Result + @ReplaceText,'')
SET @Next = @Next + @EndPattern - 1
END
END
RETURN(@Result)
END
GO
SELECT dbo.PatternReplace('bababe', 'b%b', 'c')
DROP FUNCTION dbo.PatternReplace
?
It's no big deal, I admit. Hope to have a more closer look at this function tomorrow at work with a faster online connection.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
I'm not a big fan of that modification, generally-speaking, as it differs from the functionality provided by the REPLACE function itself...
Observe:
SELECT dbo.PatternReplace('abc', '', NULL)
SELECT REPLACE('abc', '', NULL)
Both of these return NULL in my version; however, in your version the PatternReplace returns an empty string.
On the flip side, though, you appear to have fixed a bug in my version:
SELECT dbo.PatternReplace(NULL, '', 'abc')
SELECT REPLACE(NULL, '', 'abc')
In my version, PatternReplace returns an empty string, whereas REPLACE returns NULL. So I think the solution is somewhere between our two versions. I'll see if I can find the version that returns NULL in both cases ... Good catch pushing it in the right direction!
--
Adam Machanic
whoisactive -
Here are more test cases... Turns out this thing (my version) is a mess!
SELECT dbo.PatternReplace(NULL, '', 'abc')
SELECT REPLACE(NULL, '', 'abc')
SELECT dbo.PatternReplace('abc', '', NULL)
SELECT REPLACE('abc', '', NULL)
SELECT dbo.PatternReplace('abc', NULL, '')
SELECT REPLACE('abc', NULL, '')
SELECT dbo.PatternReplace('abc', 'b', '')
SELECT REPLACE('abc', 'b', '')
SELECT dbo.PatternReplace('adc', 'b', '')
SELECT REPLACE('adc', 'b', '')
--
Adam Machanic
whoisactive -
Updated version of the UDF posted here:
http://sqljunkies.com/WebLog/amachanic/articles/PatternReplace.aspx
Thanks again for the input, Frank!
--
Adam Machanic
whoisactive -
It's me who is thankful! Your UDF is really cute. Good to see your blog is back online again. I tried this morning (European time) and got some strange runtime error. Unfortunately I haven't kept the error message.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
SQLJunkies site unfortunately has a bug and they have to restart the blog once or twice a day
-- luckily they're upgrading this month to a newer version so hopefully we won't see that issue anymore. Unfortunately, they've already lost a lot of quality bloggers as a result...Anyway, FYI in English (at least, American English), "cute" probably isn't the best term to use for something like this -- it brings to mind puppies and meadows full of flowers...
--
Adam Machanic
whoisactive -
Hey, I can always blame it on the language barrier. That's a huge advantage at times
Here's the revamped version:Your UDF is really nice!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply