Find and replace a string in sql server 2005

Question

I am supposed to remove the following from the address field.

1. Replace all words named as 'Flat' to empty space, also remove the number that comes along with it.

eg. I have word called as 'Flat 234 5th Street'.

It should be replaced as 5th Street.

I gave the query as

select   
    ltrim(rtrim( substring ('Flat 123 5th Street', charindex('Flat ','Flat 123 5th Street') + 5, len ('Flat 123 5th Street'))))

It returned

123 5th Street

Now I have to find whether till the next occurrence of space, I have a numeric value or not.

If Numeric, then remove it, else leave it .

Can anyone help please.

Regards, Hema

Yes Marc_S, i should have done it (edited). I can not do in any other language. Supposed to do in T-SQL only.

Hi LittleBobbyTales, Thanks for the Answer. Actually its not a standard format, I might have only Flat 123

or Flat 123 5th Street 1st Main Road

or 1st Main Flat 1

There is no rule that we will have 1 numbers or 2 numbers after the word Flat. There may or may not be numbers at all.

It can be either way.

Answer 1

You could use a scalar-valued function to strip the flat part out. The tricky part is how to check if a word is a number: @word like '%[^0-9]%' does that by looking for a character that is not 0-9. Full example:

if OBJECT_ID('fn_StripFlat') is not null
    drop function fn_StripFlat
go
create function dbo.fn_StripFlat(
    @street varchar(150))
returns varchar(150)
as begin
    declare @word varchar(150)
    declare @result varchar(150)
    declare @cur int
    declare @next int
    declare @in_flat bit

    set @cur = 1
    while 1=1
        begin
        set @next = CHARINDEX(' ', @street, @cur)
        if @next = 0
            set @word = SUBSTRING(@street, @cur, len(@street) - @cur + 1)
        else
            set @word = SUBSTRING(@street, @cur, @next - @cur)

        if @word = 'flat'
            begin
            set @in_flat = 1
            end
        else if @word like '%[^0-9]%'
            begin
            set @in_flat = 0
            set @result = IsNull(@result + ' ','') + @word
            end

        if @next = 0
            break
        set @cur = @next + 1
        end
    return IsNull(@result,'')
end
go

Test code:

declare @Streets table (street varchar(150))
insert @Streets
          select 'Flat 234 5th Street'
union all select 'Flat 123 456 5th Street 1st Main Road'
union all select '1st Main Flat 1'
union all select '5th Street 1st Main Road'
union all select 'FlatStreet'
union all select ''

select  street
,       dbo.fn_StripFlat(street)
from    @Streets

This prints:

Flat 234 5th Street                     5th Street
Flat 123 456 5th Street 1st Main Road   5th Street 1st Main Road
1st Main Flat 1                         1st Main
5th Street 1st Main Road                5th Street 1st Main Road
FlatStreet                              FlatStreet