How do I split a sentence in SQL Server 2005

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(开发者_StackOverflow社区' ',@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.

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

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.

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

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