如何修剪英国邮政编码
我只想显示英国邮政编码的首字母.例如，如果它的 WV14 7AR 那么我只想显示 WV
I would like to only show the first letters of a UK postcode. For example if its WV14 7AR then i only want to show WV
我试过了:
DECLARE @v VARCHAR(30)
SET @v='WV14 7AR'
SELECT LEFT(@v,LEN(@v)-6)
返回:WV
但是如果邮政编码是:WV4 那么它会显示:W 这是错误的.
However if the postcode is: WV4 then it will show: W and this is wrong.
请记住，您可以获得一个英国邮政编码，它可以是 1 个字母！伯明翰邮政编码为:B1 2AR，因此 LEFT 建议将失败.
Bare in mind you can get a UK postcode which could be 1 letter! A Birmingham postcode is: B1 2AR and therefore LEFT suggestion will fail.
推荐答案
这将显示仅显示英国邮政编码的首字母:
With Src (Postcode) As (
Select 'B1 1AB' Union All
Select 'W1A 1AB' Union All
Select 'WV14 1AB' Union All
Select 'GIR 1AB'
)
Select
Postcode,
RTRIM(LEFT(Postcode, PATINDEX('%[0-9]%', Postcode) - 1)) As FirstLetters
From Src
<小时>
Postcode FirstLetters
-------- ------------
B1 1AB B
W1A 1AB W
WV14 1AB WV
GIR 1AB GIR
<小时>
Upd:可以使用
Where Postcode Like '%[0-9]%'
甚至更严格
Where Postcode Like '[a-z]%[0-9]%[a-z]'
或处理
CASE WHEN Postcode Like '%[0-9]%'
THEN RTRIM(LEFT(Postcode, PATINDEX('%[0-9]%', Postcode) - 1))
ELSE Postcode
END As FirstLetters
相关文章