如何修剪英国邮政编码 [英] How to trim UK postcodes

问题描述

我只想显示英国邮政编码的首字母.例如，如果它的 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

这篇关于如何修剪英国邮政编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！