How do I split a sentence in SQL Server 2005

Question

I have a column named Address in a table. I have values such as

12-15 Hardley Street    
2A-C Hardley Street    
A-2c Hardley Street    
A-B Hardley Street

I am required to keep the hyphen(-) intact in the first three rows.

i.e. If there is a number in the word that contains the hyphen(-), I should keep it, else I should replace with a space.

So the column should be replaced as below

12-15 Hardley Street    
2A-C Hardley Street    
A-2c Hardley Street    
A B Hardley Street

My small brain cannot comprehend this. Any help pls.

p.s: I would also like to add that it is not necessary that the first word will contain the hyphen.

The data can be as

Hardley Street 2A-C 

or

2A-C

Thanks one and all for your reply and comments.

There has been one more pre-requisite, I am supposed to do in an update query.

declare @data nvarchar(200)
set @data='12-12 ORANGE-RED'
select @data= 
    case left(@data,charindex(' ',@data))
        when '' then 
                CASE PATINDEX ('%[0-9]%',@data)
                    when 0 then replace(@data,'-',' ')
                    else @data
                END
        else 
                CASE PATINDEX ('%[0-9]%',left(@data,charindex(' ',@data)))
                    when 0 then replace(left(@data,charindex(' ',@data)),'-',' ')+'-- LEFT MOST WORD REPLACE'
                    else 
                        CASE charindex (' ',substring(@data,charindex(' ',@data)+1,len(@data)))
                            WHEN 0 THEN 
                                CASE PATINDEX ('%[0-9]%',substring(@data,charindex(' ',@data)+1,len(@data)))
                                    when 0 then left(@data,charindex(' ',@data))+ replace(substring(@data,charindex(' ',@data)+1,len(@data)),'-',' ') +'--RIGHT MOST REPLACE'
                                    else @data + '--struggling here'
                                END
                            END

                END
    end
where @data like '%-%'
select @data

I have tried the above I will be updating the table as

update tblname set @columnName= 
    --lines of coding
where @columnName like '%-%'

I am not able to solve this, and to add to my woes, the data will be inconsistent.

I assumed the data to be of two words, such as

2A-C Hardley Street    
A-2c Hardley Street    
A B Hardley Street

But the client said that he will be having data as

  Hardley Street  22-23  BO'NESS
    A-2c Hardley Street    
    Hardley Street 12B Stratford-upon-avon

I cannot create a function, the reason being we are going to update a table and format it. If I create a function that I should call it for every other row, which is time consuming.

Kindly do not suggest SSIS as that has already been ruled out.

Any ideas will be very helpful.

Answer 1

This is not the sort of thing SQL was built to do. Logic like this is better served (and more easily done) in your application layer if possible.

Answer 2

This would be easiest to do with CLR integration and Regular Expressions.

Answer 3

If you separating data with empty space, and your first column does not have empty spaces you can try following

    select 
     LEFT(a,charindex(' ',a)-1) as leftSide
    ,SUBSTRING(a,charindex(' ',a)+1,LEN(a)) as rightSide
from
(

select '12-15 Hardley Street    ' as a
union all
select '2A-C Hardley Street    '
union all
select 'A-2c Hardley Street    '
union all
select 'A-B Hardley Street'

) as a

If you have empty spaces inside left side then you should describe more closely rules of adding data, and perhaps try using Regex as it is mentioned

Answer 4

create function ParseAddress(@Address varchar(255))
    returns varchar(255)
as
BEGIN
    declare @result varchar(255)
    declare @str varchar(255) 
    declare @i smallint
    declare @j smallint 
    declare @Separator char(1)

    set @Separator = ' '

    set @str = @Address 

    set @str = REVERSE(@str) 
    set @j = PATINDEX ('%[0-9]%',LEFT(@str,CHARINDEX(char(10),@str))) 

    if @j > 0 set @Separator = '-'

    set @i = PATINDEX ('%-%',@str)  

    set @result = REVERSE( LEFT(@str,@i-1) + @Separator + RIGHT(@str,LEN(@str) - @i)) 

RETURN @result
end