Read Letters until first Number

Question

Post reply

Read Letters until first Number

dopydb

SSCertifiable

Points: 6687
More actions
November 14, 2014 at 9:31 am

#313027

hi
i need to extract the letters of a postcode up until the first occurrence of a number
eg
BT67 8kT = BT
B68 4HY = B
anyone any tips for me?
mal
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

You need a combination of LEFT with PATINDEX.

SELECT LEFT( String, PATINDEX('%[0-9]%', String) - 1)

FROM (VALUES('BT67 8kT'),('B68 4HY'))x(String)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

dopydb SSCertifiable Points: 6687 More actions · Answer 2

hi Luis

apols, i am not sure i am following that

if i am reading the postcode field from table customer, how would you structure your query?

thanks for helping

mal

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

Change the Table Value Constructor to use your table and change the column to your column.

I'm using the exact name so you can search for the items yourself and be able to learn more.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

dopydb SSCertifiable Points: 6687 More actions · Answer 4

thanks Luis ,and you are just right making me research what i am doing 😉

SELECT Customer,ShipPostalCode,left(ShipPostalCode, ((PATINDEX('%[0-9]%', ShipPostalCode)-1))

FROM ArCustomer

mister.magoo SSC-Forever Points: 47068 More actions · Answer 5

dopydb (11/14/2014)
thanks Luis ,and you are just right making me research what i am doing 😉
SELECT Customer,ShipPostalCode,left(ShipPostalCode, ((PATINDEX('%[0-9]%', ShipPostalCode)-1))
FROM ArCustomer

what do you want to do when you come across an invalid postcode that has no numbers? Error out or handle it?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

dopydb SSCertifiable Points: 6687 More actions · Answer 6

Hi dd

All postcodes 'should' have a number , but I guess better to have a solution that's a bit more robust. What would you do?

Mal

mister.magoo SSC-Forever Points: 47068 More actions · Answer 7

dopydb (11/14/2014)
thanks Luis ,and you are just right making me research what i am doing 😉
SELECT Customer,ShipPostalCode,left(ShipPostalCode, ((PATINDEX('%[0-9]%', ShipPostalCode)-1))
FROM ArCustomer

SELECT Customer,ShipPostalCode,left(ShipPostalCode, ((PATINDEX('%[0-9]%', ShipPostalCode+'1')-1))

FROM ArCustomer

I would just append a number such as '1' to the ShipPostalCode to ensure patindex always returns a value (unless ShipPostalCode is null)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);