November 8, 2011 at 5:33 am
Hello,
I am trying to retrieve the first part and the first letter on the second part of a UK postcode, i.e. if a postcode is AB12 3CB, I would like AB12 3.
I am using the script below, but can't work out how to get the first character after the space.
SELECT customer, postcode, LEFT(postcode, CHARINDEX(' ', postcode, +1)) AS sector
FROM cust
WHERE (postcode IS NOT NULL) AND (country = 'UK')
Can anyone help please?
Thank you in advance. Damion
November 8, 2011 at 6:41 am
Damion (11/8/2011)
Hello,I am trying to retrieve the first part and the first letter on the second part of a UK postcode, i.e. if a postcode is AB12 3CB, I would like AB12 3.
I am using the script below, but can't work out how to get the first character after the space.
SELECT customer, postcode, LEFT(postcode, CHARINDEX(' ', postcode, +1)) AS sector
FROM cust
WHERE (postcode IS NOT NULL) AND (country = 'UK')
Can anyone help please?
Thank you in advance. Damion
How's this?
DECLARE @postcode VARCHAR(20)
SET @postcode = 'AB12 3CB'
SELECT SUBSTRING(postcode,start,length-start-fromEnd) AS newPostCode
FROM (SELECT MAX(PATINDEX([matched],@postcode)) AS start,
MAX(PATINDEX([ReverseMatch],reverse(@postcode+' ')))-1 AS fromEnd,
len(@postcode+'||') AS [length], @postcode AS postcode
FROM (SELECT '[A-Z][A-Z0-9] [0-9]%', '%[0-9] [A-Z0-9][A-Z]' UNION
SELECT '[A-Z][A-Z0-9][A-Z0-9] [0-9]%', '%[0-9] [A-Z0-9][A-Z0-9][A-Z]' UNION
SELECT '[A-Z][A-Z0-9][A-Z0-9][A-Z0-9] [0-9]%', '%[0-9] [A-Z0-9][A-Z0-9][A-Z0-9][A-Z]') a(matched,ReverseMatch)
) work
"Ah, but Cadavre, how does it deal with a big table?" I hear you ask.
Better than you'd have thought, but not particularly well 🙂
--Standard TestEnvironment of 1,000,000 rows of random-ish data
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
SET NOCOUNT ON
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * 30000 + CAST('1945' AS DATETIME) AS randomDate,
ABS(CHECKSUM(NEWID())) AS randomBigInt,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS randomSmallInt,
RAND(CHECKSUM(NEWID())) * 100 AS randomSmallDec,
RAND(CHECKSUM(NEWID())) AS randomTinyDec,
RAND(CHECKSUM(NEWID())) * 100000 AS randomBigDec,
CONVERT(VARCHAR(6),CONVERT(MONEY,RAND(CHECKSUM(NEWID())) * 100),0) AS randomMoney,
CHAR((ABS(CHECKSUM(NEWID())) % 26) + 65) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 65) +
CONVERT(CHAR(1),(ABS(CHECKSUM(NEWID())) % 9) + 1) + CONVERT(CHAR(1),(ABS(CHECKSUM(NEWID())) % 9) + 1)
+ ' ' + CONVERT(CHAR(1),(ABS(CHECKSUM(NEWID())) % 9) + 1) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 65) +
CHAR((ABS(CHECKSUM(NEWID())) % 26) + 65) AS randomPseduoPostCode
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
PRINT '========== BASELINE =========='
SET STATISTICS TIME ON
SELECT COUNT(*) FROM #testEnvironment
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== Split PostCode =========='
SET STATISTICS TIME ON
SELECT SUBSTRING(postcode,start,length-start-fromEnd) AS newPostCode
FROM (SELECT MAX(PATINDEX([matched],randomPseduoPostCode)) AS start,
MAX(PATINDEX([ReverseMatch],reverse(randomPseduoPostCode+' ')))-1 AS fromEnd,
len(randomPseduoPostCode+'||') AS [length], randomPseduoPostCode AS postcode
FROM (SELECT '[A-Z][A-Z0-9] [0-9]%', '%[0-9] [A-Z0-9][A-Z]' UNION
SELECT '[A-Z][A-Z0-9][A-Z0-9] [0-9]%', '%[0-9] [A-Z0-9][A-Z0-9][A-Z]' UNION
SELECT '[A-Z][A-Z0-9][A-Z0-9][A-Z0-9] [0-9]%', '%[0-9] [A-Z0-9][A-Z0-9][A-Z0-9][A-Z]') a(matched,ReverseMatch)
CROSS JOIN #testEnvironment
GROUP BY randomPseduoPostCode
) work
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
SET NOCOUNT OFF
========== BASELINE ==========
SQL Server Execution Times:
CPU time = 124 ms, elapsed time = 33 ms.
================================================================================
========== Split PostCode ==========
SQL Server Execution Times:
CPU time = 33015 ms, elapsed time = 25808 ms.
================================================================================
November 8, 2011 at 7:33 am
Good work, Cadavre, especially generating all those random postcodes! The following are also legal postcodes, and I've checked that your code also works with them:
'N12 4DB'
'SW1A 4DB'
'W1B 4DB'
'L1 4DB'
'LS1 4DB'
If we assume that the postcode will always have the two parts separated by a single space, we can simplify your code somewhat:
SET NOCOUNT ON
PRINT '========== BASELINE =========='
SET STATISTICS TIME ON
SELECT COUNT(*) FROM #testEnvironment
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== Split PostCode =========='
SET STATISTICS TIME ON
SELECT SUBSTRING(postcode,start,length-start-fromEnd) AS newPostCode
FROM (SELECT MAX(PATINDEX([matched],randomPseduoPostCode)) AS start,
MAX(PATINDEX([ReverseMatch],reverse(randomPseduoPostCode+' ')))-1 AS fromEnd,
len(randomPseduoPostCode+'||') AS [length], randomPseduoPostCode AS postcode
FROM (SELECT '[A-Z][A-Z0-9] [0-9]%', '%[0-9] [A-Z0-9][A-Z]' UNION
SELECT '[A-Z][A-Z0-9][A-Z0-9] [0-9]%', '%[0-9] [A-Z0-9][A-Z0-9][A-Z]' UNION
SELECT '[A-Z][A-Z0-9][A-Z0-9][A-Z0-9] [0-9]%', '%[0-9] [A-Z0-9][A-Z0-9][A-Z0-9][A-Z]') a(matched,ReverseMatch)
CROSS JOIN #testEnvironment
GROUP BY randomPseduoPostCode
) work
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== CHARINDEX =========='
SET STATISTICS TIME ON
SELECT LEFT(randomPseduoPostCode,CHARINDEX(' ',randomPseduoPostCode)+1)
FROM #testEnvironment
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
SET NOCOUNT OFF
========== BASELINE ==========
SQL Server Execution Times:
CPU time = 157 ms, elapsed time = 23 ms.
================================================================================
========== Split PostCode ==========
SQL Server Execution Times:
CPU time = 26405 ms, elapsed time = 38324 ms.
================================================================================
========== CHARINDEX ==========
SQL Server Execution Times:
CPU time = 578 ms, elapsed time = 17869 ms.
================================================================================
John
November 8, 2011 at 9:52 am
Thank you very much! All sorted.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply