May 26, 2016 at 5:19 am
Hi all,
In a nutshell I'm struggling to extract postcodes from a table that have two spaces in the middle (i.e. aa99 9aa rather than aa99 9aa), could anyone please help me with the tsql?
The postcodes are in the following formats:
A9 9AA
A99 9AA
AA9 9AA
AA99 9AA
A9A 9AA
AA9A 9AA
I've so far tried using the patindex function:
select p_code
from Postcode
where
patindex('[A-Z][0-9] [0-9][A-Z][A-Z]', p_code) = 0 and
patindex('[A-Z][0-9][0-9] [0-9][A-Z][A-Z]', p_code) = 0 and
patindex('[A-Z][A-Z][0-9] [0-9][A-Z][A-Z]', p_code) = 0 and
patindex('[A-Z][A-Z][0-9][0-9] [0-9][A-Z][A-Z]', p_code) = 0 and
patindex('[A-Z][0-9][A-Z] [0-9][A-Z][A-Z]', p_code) = 0 and
patindex('[A-Z][A-Z][0-9][A-Z] [0-9][A-Z][A-Z]', p_code) = 0;
along with the like predicate but to no avail.
Many thanks in advance.
email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk
May 26, 2016 at 5:33 am
i think you can just search for double spaces, and not whether they follow the rest/standard formatting for like rules right?
if double spaces are not allowed, you could just replace all of them with a simple update/replace.
also your current rule should be OR and not AND; one string cannot match All six patterns at the same time, which is what the AND is doing.
;WITH Postcode([p_code])
AS
(
SELECT 'A9 9AA' UNION ALL
SELECT 'A99 9AA' UNION ALL
SELECT 'AA9 9AA' UNION ALL
SELECT 'AA99 9AA' UNION ALL
SELECT 'A9A 9AA' UNION ALL
SELECT 'AA9A 9AA'
)
SELECT * FROM Postcode WHERE p_code LIKE'% %'
Lowell
May 26, 2016 at 5:47 am
Reading the question I too thought why not you use a like '% %' or a '% % %' if they are not adjacent. Unless we are not understanding the question correctly
May 26, 2016 at 6:13 am
your original six part WHERE finds everything that is "OK", ie does not match the double spaced indexes.
this might work as w to find your exceptions and outliers:
select p_code
from Postcode
where
patindex('[A-Z][0-9] [0-9][A-Z][A-Z]', p_code) > 0 OR
patindex('[A-Z][0-9][0-9] [0-9][A-Z][A-Z]', p_code) > 0 OR
patindex('[A-Z][A-Z][0-9] [0-9][A-Z][A-Z]', p_code) > 0 OR
patindex('[A-Z][A-Z][0-9][0-9] [0-9][A-Z][A-Z]', p_code) > 0 OR
patindex('[A-Z][0-9][A-Z] [0-9][A-Z][A-Z]', p_code) > 0 OR
patindex('[A-Z][A-Z][0-9][A-Z] [0-9][A-Z][A-Z]', p_code) > 0;
Lowell
May 26, 2016 at 6:28 am
Lowell (5/26/2016)
your original six part WHERE finds everything that is "OK", ie does not match the double spaced indexes.this might work as w to find your exceptions and outliers:
select p_code
from Postcode
where
patindex('[A-Z][0-9] [0-9][A-Z][A-Z]', p_code) > 0 OR
patindex('[A-Z][0-9][0-9] [0-9][A-Z][A-Z]', p_code) > 0 OR
patindex('[A-Z][A-Z][0-9] [0-9][A-Z][A-Z]', p_code) > 0 OR
patindex('[A-Z][A-Z][0-9][0-9] [0-9][A-Z][A-Z]', p_code) > 0 OR
patindex('[A-Z][0-9][A-Z] [0-9][A-Z][A-Z]', p_code) > 0 OR
patindex('[A-Z][A-Z][0-9][A-Z] [0-9][A-Z][A-Z]', p_code) > 0;
Many thanks for your help with this one, apologies if I'm coming across a little dim! I did indeed try to use LIKE '% %' at first but to no avail. Tried your solutions above and again no dice!
Not sure if this will help but this is the test table and data I've been using to develop the script:
CREATE TABLE [dbo].[Postcode](
[p_code] [nchar](10) NULL
) ON [PRIMARY]
GO
insert into dbo.Postcode
values
('L2 3SW'),
('M16 0RA'),
('NW3 2RR'),
('EH12 9DN'),
('W1A 1HQ'),
('SW1A 2AA'),
('SN2 2HL'),
('SN2 2HL'),
('SN25 4DL'),
('SN25 4DL'),
('L2 3SW'),
('M16 0RA'),
('NW3 2RR'),
('EH12 9DN'),
('W1A 1HQ'),
('SW1A 2AA');
GO
email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk
May 26, 2016 at 6:36 am
still not sure what are after....thanks for the sample data, would be good if you posted expected results based on that sample..
maybe...??
select REPLACE(p_code,' ',' ') pcode
from Postcode
where
patindex('[A-Z][0-9] [0-9][A-Z][A-Z]', p_code) = 0 and
patindex('[A-Z][0-9][0-9] [0-9][A-Z][A-Z]', p_code) = 0 and
patindex('[A-Z][A-Z][0-9] [0-9][A-Z][A-Z]', p_code) = 0 and
patindex('[A-Z][A-Z][0-9][0-9] [0-9][A-Z][A-Z]', p_code) = 0 and
patindex('[A-Z][0-9][A-Z] [0-9][A-Z][A-Z]', p_code) = 0 and
patindex('[A-Z][A-Z][0-9][A-Z] [0-9][A-Z][A-Z]', p_code) = 0;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 26, 2016 at 6:39 am
Probably this could help. The problem is that you have a char(10) and you're looking for 8-character strings.
SELECT *
FROM dbo.Postcode
WHERE p_code LIKE N'___ ___ ';
May 26, 2016 at 7:41 am
Luis Cazares (5/26/2016)
Probably this could help. The problem is that you have a char(10) and you're looking for 8-character strings.
SELECT *
FROM dbo.Postcode
WHERE p_code LIKE N'___ ___ ';
Good call! This now works with some adjustments for the various other character lengths, many thanks.
I also figured we can extract the entire data set (15000 records) from the address table in the original database (not the test one I'm working on), export it to Excel and do a simple find and replace (i.e. find what = 2 spaces, replace with = 1 space). This then removes the errant spaces from the postcodes and we can import the data back into the database!!!
Thanks for all the help on this one though as I've learnt a lot.
Many thanks.
Mark.
email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk
May 27, 2016 at 12:41 pm
export it to Excel and do a simple find and replace
What is wrong with
UPDATE yourtable
SET Postcode = Replace(Postcode, ' ', ' ')
FROM yourtable
WHERE postcode LIKE N'___ ___ ';
No need to export or re-import...
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 20, 2016 at 6:21 am
EdVassie (5/27/2016)
export it to Excel and do a simple find and replace
What is wrong with
UPDATE yourtable
SET Postcode = Replace(Postcode, ' ', ' ')
FROM yourtable
WHERE postcode LIKE N'___ ___ ';
No need to export or re-import...
Or even, to deal with trailing spaces;
UPDATE dbo.Postcode
SET p_code = RTRIM(REPLACE(p_code,' ', ' '))
FROM dbo.PostCode
...
June 20, 2016 at 7:23 am
HappyGeek (6/20/2016)
EdVassie (5/27/2016)
export it to Excel and do a simple find and replace
What is wrong with
UPDATE yourtable
SET Postcode = Replace(Postcode, ' ', ' ')
FROM yourtable
WHERE postcode LIKE N'___ ___ ';
No need to export or re-import...
Or even, to deal with trailing spaces;
UPDATE dbo.Postcode
SET p_code = RTRIM(REPLACE(p_code,' ', ' '))
FROM dbo.PostCode
That won't do any good. The column is an nchar, so the trailing spaces will be added.
June 20, 2016 at 10:04 am
Luis Cazares (6/20/2016)
HappyGeek (6/20/2016)
EdVassie (5/27/2016)
export it to Excel and do a simple find and replace
What is wrong with
UPDATE yourtable
SET Postcode = Replace(Postcode, ' ', ' ')
FROM yourtable
WHERE postcode LIKE N'___ ___ ';
No need to export or re-import...
Or even, to deal with trailing spaces;
UPDATE dbo.Postcode
SET p_code = RTRIM(REPLACE(p_code,' ', ' '))
FROM dbo.PostCode
That won't do any good. The column is an nchar, so the trailing spaces will be added.
Correct, never spotted that, lesson learned, read the definition!!!
...
July 8, 2016 at 9:57 am
Would the code below work? Would capture anything with 2 or more spaces?
SELECT *
FROM dbo.Postcode AS p
WHERE LEN(RTRIM(p_code)) - LEN(REPLACE(RTRIM(p_code),' ', '')) >=2
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply