December 23, 2015 at 7:16 am
Hi I have a ZipCode column in the address table and this column has some invalid zipcodes like 'a36477','445-2'.
I am trying to write a query to select only those zip codes which will have only numbers, no alphabets and other special characters.
How Can I do it?
Thanks.
December 23, 2015 at 7:27 am
There are several ways to achieve the desired results.
Here is a sample function that will help you get started:
CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%', @strText) > 0
BEGIN
SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
END
RETURN @strText
END
Sample usage:
DECLARE @string NVARCHAR(MAX) = N'P1234-T6'
SELECT [dbo].[fnRemoveNonNumericCharacters](@string)
Will return:
12346
gsc_dba
December 23, 2015 at 7:29 am
Assuming you are trying to only return those rows with numbers only and not return rows with any other characters you do this pretty easily.
if OBJECT_ID('tempdb..#ZipCodes') is not null
drop table #ZipCodes
create table #ZipCodes
(
Zip varchar(10)
)
insert #ZipCodes
values
('a36477')
,('445-2')
,('12345')
, ('#2-f8jd')
select *
from #ZipCodes
where Zip not like '%[^0-9]%'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 23, 2015 at 7:33 am
Sean Lange (12/23/2015)
Assuming you are trying to only return those rows with numbers only and not return rows with any other characters you do this pretty easily.
if OBJECT_ID('tempdb..#ZipCodes') is not null
drop table #ZipCodes
create table #ZipCodes
(
Zip varchar(10)
)
insert #ZipCodes
values
('a36477')
,('445-2')
,('12345')
, ('#2-f8jd')
select *
from #ZipCodes
where Zip not like '%[^0-9]%'
Misread OP request - the function I provided returns only the numbers from the string (data cleansing)...
gsc_dba
December 23, 2015 at 7:37 am
gsc_dba (12/23/2015)
There are several ways to achieve the desired results.Here is a sample function that will help you get started:
CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))RETURNS VARCHAR(1000)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%', @strText) > 0
BEGIN
SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
END
RETURN @strText
END
Sample usage:
DECLARE @string NVARCHAR(MAX) = N'P1234-T6'SELECT [dbo].[fnRemoveNonNumericCharacters](@string)
Will return:
12346
I don't think is what the OP is looking for but this is an excellent opportunity to learn about tally tables. We can do this same kind of non-numeric stripping using a set based approach with a tally table instead of looping.
I keep a view as a tally table on my system. Here is the tally table code.
create View [dbo].[cteTally] as
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select N from cteTally
Now we have an amazing amount of power at our fingertips. We can leverage this for things like stripping non-numeric characters from a string quite easily. Something like this.
CREATE FUNCTION [dbo].[StripNonNumeric]
(
@OriginalText VARCHAR(8000)
)
RETURNS TABLE AS
RETURN
select STUFF(
(
SELECT SUBSTRING(@OriginalText, t.N, 1)
FROM cteTally t
WHERE ASCII(SUBSTRING(@OriginalText, t.N, 1)) BETWEEN 48 AND 57
AND n <= len(@OriginalText)
FOR XML PATH('')
), 1 ,0 , '') AS CleanedText
--EDIT--
Here is an example of how you could utilize this.
if OBJECT_ID('tempdb..#ZipCodes') is not null
drop table #ZipCodes
create table #ZipCodes
(
Zip varchar(10)
)
insert #ZipCodes
values
('a36477')
,('445-2')
,('12345')
, ('#2-f8jd')
select *
from #ZipCodes
cross apply [StripNonNumeric](Zip)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 23, 2015 at 7:40 am
Very slick solution Sean, kudos!
gsc_dba
December 23, 2015 at 7:51 am
gsc_dba (12/23/2015)
Very slick solution Sean, kudos!
Slick, but definitely not what the OP was asking for. The OP clearly said he wanted to select the values where ZIP was numeric only.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 23, 2015 at 7:55 am
Alvin Ramard (12/23/2015)
gsc_dba (12/23/2015)
Very slick solution Sean, kudos!Slick, but definitely not what the OP was asking for. The OP clearly said he wanted to select the values where ZIP was numeric only.
Which is what I posted in my first post. 🙂 I just took the opportunity to help gsc_dba learn another way to get rid of a loop.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 23, 2015 at 7:59 am
Sean Lange (12/23/2015)
Alvin Ramard (12/23/2015)
gsc_dba (12/23/2015)
Very slick solution Sean, kudos!Slick, but definitely not what the OP was asking for. The OP clearly said he wanted to select the values where ZIP was numeric only.
Which is what I posted in my first post. 🙂 I just took the opportunity to help gsc_dba learn another way to get rid of a loop.
And much appreciated 🙂
I always default to why you want to exclude data (and cleanse), rather than exclude - but that's just me!
gsc_dba
December 23, 2015 at 8:01 am
sql_novice_2007 (12/23/2015)
Hi I have a ZipCode column in the address table and this column has some invalid zipcodes like 'a36477','445-2'.I am trying to write a query to select only those zip codes which will have only numbers, no alphabets and other special characters.
How Can I do it?
Thanks.
Starting with v2012, we now have the TRY_CAST function which simply returns attempts to convert a string to a specific data type, and then returns either the converted value or NULL when convertion fails.
https://msdn.microsoft.com/en-us/library/hh974669(v=sql.110).aspx
You can select where ZipCode converts to an integer, or if ZipCode cannot exceed 5 digits, you can select where ZipCode converts to numeric(5).
SELECT ZipCode FROM Address WHERE TRY_CAST( ZipCode AS INT ) IS NOT NULL;
SELECT ZipCode FROM Address WHERE TRY_CAST( ZipCode AS NUMERIC(5) ) IS NOT NULL;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply