locked
SQL query update not working RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    User456628452 posted

    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 Addresses
    Id AddressLine1
    1 12-2-50 Flat No
    2 211-G-40 Flat No
    Thursday, November 7, 2019 4:39 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    User-719153870 posted

    Hi marya,

    marya

    RIGHT(AddressLine1, CHARINDEX(' ', REVERSE(AddressLine1)) -1)

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

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 7, 2019 8:33 AM