July 24, 2015 at 3:41 am
I am struggling with what should be a simple issue....I want to search a postcode field (varchar column) for any characters that shouldn't be there, ie outwith A-Z and 0-9 and a space.
This would seem on the face of it quite easy but I am struggling and a search is not getting me what I want :crying:
Can anyone advise?
July 24, 2015 at 4:28 am
rarara (7/24/2015)
I am struggling with what should be a simple issue....I want to search a postcode field (varchar column) for any characters that shouldn't be there, ie outwith A-Z and 0-9 and a space.This would seem on the face of it quite easy but I am struggling and a search is not getting me what I want :crying:
Can anyone advise?
I borrowed heavily from the code in this thread but this should do what you need.
CREATE FUNCTION [dbo].PCodeCleaner ( @pcode VARCHAR(15))
RETURNS table
WITH SCHEMABINDING
AS
return
SELECT Pcode = (SELECT SUBSTRING(@PCode,N,1)
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)) AS x(N)
WHERE N<=LEN(@PCode)
AND
(SUBSTRING(@PCode,N,1) LIKE ('[0-9]')
OR
SUBSTRING(@PCode,N,1) LIKE ('[A-Z]')
OR
SUBSTRING(@PCode,N,1) LIKE (' ')
)
ORDER BY N
FOR XML PATH(''), TYPE).value('.','varCHAR(15)')
go
I've limited it to fifteen characters, hopefully you won't be getting any more than that but it's easy enough to adapt to more. One quick question though; what if you get more than one space?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
July 24, 2015 at 5:06 am
hi, thanks - unfortunately its not possible, since i would need to jump through hoops to add a function to this system and a #function isn't an option.
is the syntax usable within a select statement alone?
July 24, 2015 at 5:10 am
create table #PostCodes
(
PostCode varchar(15)
)
insert into #Postcodes values
('AA1 &&%((£4GG')
,('AB2 **%%6TG')
,('£FE2 %5GH')
,('BL0 9QT')
select
Postcode
,cpc.pcode
from #postcodes
cross apply
(
SELECT Pcode = (SELECT SUBSTRING(Postcode,N,1)
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)) AS x(N)
WHERE N<=LEN(Postcode)
AND
(
SUBSTRING(Postcode,N,1) LIKE ('[0-9]')
OR
SUBSTRING(Postcode,N,1) LIKE ('[A-Z]')
OR
SUBSTRING(Postcode,N,1) LIKE (' ')
)
ORDER BY N
FOR XML PATH(''), TYPE).value('.','varCHAR(15)')
)cpc
drop table #postcodes
You could do it like this if you can't create a function.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
July 24, 2015 at 7:02 am
Do you need to remove the characters that shouldn't be there?
Or simply identify the values that shouldn't be there?
The second option is far less complicated as you just need to find values that don't have invalid characters.
SELECT *
FROM #PostCodes
WHERE Postcode NOT LIKE '%[^0-9A-Z ]%'
I would also simplify the previous code.
select
Postcode
,cpc.pcode
from #postcodes
cross apply
(
SELECT Pcode = (SELECT SUBSTRING(Postcode,N,1)
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)) AS x(N)
WHERE N<=LEN(Postcode)
AND SUBSTRING(Postcode,N,1) LIKE '[0-9A-Z ]'
ORDER BY N
FOR XML PATH(''), TYPE).value('.','varCHAR(15)')
)cpc
July 24, 2015 at 7:25 am
Hi,
here is the solution....
create FUNCTION [dbo].[ufn_CheckvalidZIP] ( @pInput VARCHAR(1000) )
RETURNS BIT
BEGIN
--declare @pInput VARCHAR(1000);
-- set @pInput='abcd^'
declare @vValidLetters VARCHAR(1000), @pSearchChar CHAR(1)
set @vValidLetters= 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 ';
DECLARE @vInputLength INT
DECLARE @vIndex INT
DECLARE @vCount INT
DECLARE @vIsvalidzip bit;
SET @vCount = 0
SET @vIndex = 1
SET @vInputLength = LEN(@pInput)
set @vIsvalidzip=1;
WHILE @vIndex <= @vInputLength
BEGIN
set @pSearchChar=SUBSTRING(@pInput, @vIndex, 1);
--check whether @psearchchar exists in @validLetters...,
-- if exists, replace(@vValidLetters,@psearchchar,'') will not be the same as
-- @validLetters
-- if not exists, replace(@vValidLetters,@psearchchar,'') will be the same as
-- @validLetters
-- upon first occurrence of invalid character, no need of further checking of other
-- characters.... thats why ... break!!
if @vValidLetters = replace(@vValidLetters,@psearchchar,'')
begin
set @vIsvalidzip=0;
BREAK;
end
SET @vCount = @vCount + 1
SET @vIndex = @vIndex + 1
END
-- Select @vIsvalidzip;
RETURN @vIsvalidzip;
END
GO
/*
-- test cases: start
SELECT [dbo].[ufn_CheckvalidZIP] ( 'BCD' )-- VALID, RETURNS 1
SELECT [dbo].[ufn_CheckvalidZIP] ( 'BCD ' ) -- TRAILING SPACE -- VALID, RETURNS 1
SELECT [dbo].[ufn_CheckvalidZIP] ( 'BCD*' )-- INVALID, RETURNS 0 -- * CHARCATER NOT ALLOWED..
-- test cases end
*/
July 24, 2015 at 7:34 am
Peddi Praveen kumar (7/24/2015)
Hi,here is the solution....
That's a solution, but it's not the solution. Your lengthy function, does basically what my code does in one line.
Another simple solution would be something like this:
SELECT *
FROM #PostCodes
WHERE PATINDEX( '%[^0-9A-Z ]%', Postcode) = 0
July 24, 2015 at 8:02 am
Luis Cazares (7/24/2015)
Peddi Praveen kumar (7/24/2015)
Hi,here is the solution....
That's a solution, but it's not the solution. Your lengthy function, does basically what my code does in one line.
Another simple solution would be something like this:
SELECT *
FROM #PostCodes
WHERE PATINDEX( '%[^0-9A-Z ]%', Postcode) = 0
This syntax just isn't working, it is finding the likes of these postcode values "CT20 2AY", when all I want is a list that have an incorrect character. Presumably because of the space, but UK codes can be represented with or without a gap?
July 24, 2015 at 8:06 am
rarara (7/24/2015)
Luis Cazares (7/24/2015)
Peddi Praveen kumar (7/24/2015)
Hi,here is the solution....
That's a solution, but it's not the solution. Your lengthy function, does basically what my code does in one line.
Another simple solution would be something like this:
SELECT *
FROM #PostCodes
WHERE PATINDEX( '%[^0-9A-Z ]%', Postcode) = 0
This syntax just isn't working, it is finding the likes of these postcode values "CT20 2AY", when all I want is a list that have an incorrect character. Presumably because of the space, but UK codes can be represented with or without a gap?
Change the equal sign(=) to a greater than sign(>).
July 24, 2015 at 8:15 am
There's an unanswered question about spaces. Would multiple spaces are valid? Would they be valid only if they're within a single gap but not with multiple gaps?
Here's the code for both options (just comment and uncomment to use the wanted filter)
SELECT *
FROM #PostCodes
WHERE PATINDEX( '%[^0-9A-Z ]%', Postcode) > 0 --Invalid characters
--OR PostCode LIKE '% % %' --Multiple spaces possibly on a single gap
OR PostCode LIKE '% %[^ ]% %' --Multiple gaps
July 24, 2015 at 8:16 am
ah-ha! success...:-)
its found a ton where it is set to "." which I may filter out, but seems to be doing the trick
thanks
July 24, 2015 at 8:17 am
This would be much easier if you could use regular expressions in SQL.
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]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply