remove non-numeric values from a nvarch column

Question

Post reply

remove non-numeric values from a nvarch column

sqlfriend

SSC Guru

Points: 52794
More actions
December 9, 2014 at 7:50 pm

#292708

We have an address table with a house_num field which is a nvarchar.
Most of house numbers are numbers like 1234, 0989
but some of them have a letter behind it like 678 B, 8909 F, even some like this 123/B
we would like to remove any non-numeric letter for this column.
Is there a way to do it?
Thanks

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65144 More actions · Answer 1

LEFT(@housenumber,PATINDEX('%[^0-9]%',@housenumber+' ')-1)

Far away is close at hand in the images of elsewhere.
Anon.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

There have been some really good discussions on this very topic. See http://www.sqlservercentral.com/Forums/Topic1585850-391-1.aspx for several different approaches to the problem.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Easy enough to do as others have shown - but why turn a valid address into an invalid one?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sqlfriend SSC Guru Points: 52794 More actions · Answer 4

Thanks all.

The reason is the original code using that column will convert that column to int.

We are writing completely new application to replace the old code.

But for now the quick solution is to remove those non-numeric letter, which in our case does not really matter.