SQL query update not working[Answered] RSS

• • Reply
  marya

  Member

  17 Points

  22 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 Addresses
  

  Id	AddressLine1
  1	12-2-50 Flat No
  2	211-G-40 Flat No

• • Reply
  Yang Shen

  Contributor

  3140 Points

  983 Posts

  Re: SQL query update not working

  Nov 07, 2019 08:33 AM|Yang Shen|LINK

  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