-3

I have a dataset with housing numbers, street addresses, and zip codes. I am trying to create another column which will include the housing numbers and street addresses. I'm working with about 100,000 datapoints. Most of my addresses are fine, but it appears that TELEGRAPH St has unit numbers at the end, which I do not want. Is there a way I can ask SQL to remove the numbers at the end of all street addresses, ONLY ON TELEGRAPH ST? For instance, most streets are like "Main Street" or "Broadway" but Telegraph has "Telegraph st 400", but I want it just be Telegraph st. I've given part of my code below, but it doesn't seem to work:

Address_Line1=CASE WHEN street1 LIKE 'TELEGRAPH%' THEN (loadd1 + ' ' + REPLACE(street1, '[0-9]', '')) WHEN street1 LIKE 'M %[0-9]%' OR street1 like 'US %[0-9]%' THEN (loadd1 + ' ' + CONCAT(street1, ' HWY')) ELSE loadd1+ ' ' +street1 END

The second portion of my code "WHEN street1 like 'M %[0-9]%'... works just fine, but when I check the output, TELEGRAPH ST still has the unit number at the end.

I've also attempted (very poorly) to create a function, but that didn't work either.

CREATE FUNCTION [dbo].NoNumbers(@x VARCHAR(MAX))

RETURNS VARCHAR(MAX)

BEGIN

CASE WHEN @x LIKE '%[0-9]%'THEN SUBSTRING(@x,0,CHARINDEX('%[0-9]%',@x)) ELSE @x END

RETURN @x

END

Thanks, your help is much appreciated!

**

**

ringtshe
  • 41
  • 4
  • While asking a question, you need to provide a minimal reproducible example. Please refer to the following link: https://stackoverflow.com/help/minimal-reproducible-example Please provide the following: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;) – Yitzhak Khabinsky May 13 '21 at 17:42
  • does [this](https://stackoverflow.com/questions/13240298/remove-numbers-from-string-sql-server) answer your question? – Stu May 13 '21 at 17:55
  • Does this answer your question? [Remove numbers from string sql server](https://stackoverflow.com/questions/13240298/remove-numbers-from-string-sql-server) – Stu May 13 '21 at 17:55
  • I used the solution provided by @SubodhW below. Thanks, everyone, for your time! :) – ringtshe May 13 '21 at 19:52
  • @Stu Yes, I think that works as well, thank you! – ringtshe May 21 '21 at 23:45

1 Answers1

0

Expression REPLACE(street1, '[0-9]', '') can't replace numbers. You may create the below function and then use like

WHEN street1 LIKE 'TELEGRAPH%' THEN (loadd1 + ' ' +  dbo.RemoveNumbers(street1))

Function :

Create Function [dbo].[RemoveNumbers](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
Declare @RemoveValues as varchar(50)
Set @RemoveValues = '%[0-9]%'
While PatIndex(@RemoveValues, @Temp) > 0
    Set @Temp = Stuff(@Temp, PatIndex(@RemoveValues, @Temp), 1, '')

Return @Temp
End

Reference -

Subodh Wasankar
  • 309
  • 2
  • 6