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
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.
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