CODEWITHSUNDEEP
sqlsql-server-2008
nsilva
nsilva

Reputation: 5614

SQL Server 2008 - Strip numbers from start of string

I am using SQL Server 2008,

I have a table called locals which has records as follows:-

447455 -    1 ABERCORN COTTAGES
447456 -    12 ACACIA WALK
447457 -    13 ACTON HILL STUDIOS
447458 -    21 ADELAIDE COTTAGES
447459 -    321 ALBERT COTTAGES
456155 -    722 CLIVE COURT

What I want to do is remove all the numbers in the 'Street' column (maybe using REPLACE) so the records update to:-

447455 -   ABERCORN COTTAGES
447456  - ACACIA WALK
447457  - ACTON HILL STUDIOS
447458 -    ADELAIDE COTTAGES
447459 -    ALBERT COTTAGES
456155 -    CLIVE COURT

I have tried:-

UPDATE LOCALS 
SET Street = REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (str(50), '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '') 
where AtoZIndex > '447454' 
  and AtoZIndex < '457309'

The street column is NVARCHAR(50)

But this just made all the records blank, any suggestions on how I could fix this?

Upvotes: 2

Views: 4860

Answers (2)

Mark Brackett
Mark Brackett

Reputation: 85645

This will truncate to the first alpha character:

UPDATE LOCALS SET 
    Street = SUBSTRING(Street, PATINDEX('%[A-Z]%', Street), LEN(Street))
WHERE
    AtoZIndex > '447454' and AtoZIndex < '457309'

Upvotes: 4

iruvar
iruvar

Reputation: 23374

Hunt for the first [A-Z] character (assuming the non-number portion of the address starts in the range A-Z

select PATINDEX('%[A-Z]%', '447455 -    1 ABERCORN COTTAGES')
15

Pick up the hyphen location

select PATINDEX('% - %', '447455 -    1 ABERCORN COTTAGES')
7

Pick up the leading numeric fragment

select SUBSTRING('447455 -    1 ABERCORN COTTAGES', 1,
PATINDEX('% - %', '447455 -    1 ABERCORN COTTAGES'))
447455

Pick up the text fragment of the address

select SUBSTRING('447455 -    1 ABERCORN COTTAGES', 
PATINDEX('%[A-Z]%', '447455 -    1 ABERCORN COTTAGES'),
LEN('447455 -    1 ABERCORN COTTAGES'))
ABERCORN COTTAGES

Concatenate the fragments with a hyphen separator

Upvotes: 2

Related Questions