SQL query update not working RRS feed

  • Question

  • 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


  • User-719153870 posted

    Hi 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)
    declare @i int;
    declare @s varchar(50);
    set @i=1;
    set @s=''
    while @i<=len(@target)-len(replace(@target,'-',''))+1
    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
    return substring(@s,1,len(@s)-1)
    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