You are trying to get the first two characters of your AddressLine1 from right to left, which will return you "No".
Please refer to below demo to get your expected result set:
create table #Addresses
(
Id int identity(1,1),
AddressLine1 varchar(100)
)
insert into #Addresses values('12-2-50 Flat No'),('211-G-40 Flat No')
create function dbo.NReverse(@target varchar(50))
returns varchar(50)
as
begin
declare @i int;
declare @s varchar(50);
set @i=1;
set @s=''
while @i<=len(@target)-len(replace(@target,'-',''))+1
begin
set @s+=(select top 1 reverse(value) from string_split(@target,'-') where value not in (select top (@i-1) value from string_split(@target,'-')))+'-'
set @i+=1
end
return substring(@s,1,len(@s)-1)
endselect Id, 'Flap '+dbo.NReverse(reverse(substring(REVERSE(AddressLine1),9,len(AddressLine1)))) as AddressLine1 from #Addresses
Below is the result:
You can see from above demo, i created a function called dbo.NReverse, in this function, i will do a complex reverse to a string. Split the string by '-', reverse the splitted short strings and join them together
again.
Member
11 Points
12 Posts
SQL query update not working
Nov 07, 2019 04:39 AM|marya|LINK
sql query select not working exactly the way i want, it revrerse the last no only,
i want select AddressLine1 as Flat 21-2-05 for Id = 1 and Flat 1112-G-04 for id 2
SELECT (reverse(RIGHT(AddressLine1, CHARINDEX(' ', REVERSE(AddressLine1)) -1))) as ex FROM AddressesContributor
2060 Points
655 Posts
Re: SQL query update not working
Nov 07, 2019 08:33 AM|Yang Shen|LINK
Hi marya,
You are trying to get the first two characters of your AddressLine1 from right to left, which will return you "No".
Please refer to below demo to get your expected result set:
create table #Addresses ( Id int identity(1,1), AddressLine1 varchar(100) ) insert into #Addresses values('12-2-50 Flat No'),('211-G-40 Flat No') create function dbo.NReverse(@target varchar(50)) returns varchar(50) as begin declare @i int; declare @s varchar(50); set @i=1; set @s='' while @i<=len(@target)-len(replace(@target,'-',''))+1 begin set @s+=(select top 1 reverse(value) from string_split(@target,'-') where value not in (select top (@i-1) value from string_split(@target,'-')))+'-' set @i+=1 end return substring(@s,1,len(@s)-1) end select Id, 'Flap '+dbo.NReverse(reverse(substring(REVERSE(AddressLine1),9,len(AddressLine1)))) as AddressLine1 from #AddressesBelow is the result:
You can see from above demo, i created a function called dbo.NReverse, in this function, i will do a complex reverse to a string. Split the string by '-', reverse the splitted short strings and join them together again.
At last, Call this funtion in the query.
About custom funtion in SQL, please refer to Create User-defined Functions (Database Engine).
Best Regard,
Yang Shen