@strA and @strB are two variables
@strA = "055367911126753316"
@strB = "00055367"
how to find the common part "055367" and remove it from string A, using SQL server query without looping? the result should be "911126753316"
be noted that always string A begin by the end part of string B
You can use replace in sql server as below:
declare @strA varchar(50) = '055367911126753316'
declare @strB varchar(50) = '00055367'
select replace(@strA,right(@strB,len(@strB)-2),'')
If it is in different columns in a table you can use as below:
create table #yourcolumns ( cola varchar(50), colb varchar(50))
insert into #yourcolumns (cola, colb) values
('055367911126753316', '00055367')
select replace(cola,right(colb,len(colb)-2),'') from #yourcolumns
I think we need to go for substring in your case as you are looking for startswith
select SUBSTRING(cola,CHARINDEX(LEFT(REVERSE(colb),1),cola)+1,len(cola)) from #yourcolumns
055367
Try This, works dynamically.
declare @StrA Nvarchar(250)='055367911126753316',@StrB Nvarchar(250)='0055367'
select right(@StrA,len(@strA)-PATINDEX('%[^'+@StrB+']%',@strA)+1)
It YES with looping, But it work....
create function MyFn (@A nvarchar(max), @B nvarchar(max))
returns nvarchar(max)
as
begin
declare @I int, @L int, @SUB nvarchar(max)
select @I = 1, @L = len(@B)
while @I<@L begin
set @SUB = substring(@B,@I,@L-@I+1)
if charindex(@SUB,@A,1) > 0 begin
set @A = replace(@A, @SUB, '')
break
end
set @I = @I + 1
end
return @A
end
If each string A starts with 000 and each string b just contains one 0 this should retrieve the correct result
WITH CTE AS (SELECT [StringA], [StringB],REPLACE([StringB], '000', '0') [tbd]
FROM YourTable)
SELECT REPLACE(YourTable.[StringA], CTE.[tbd], ''), YourTable.[StringA]
FROM YourTable
JOIN CTE ON YourTable.[StringA]= CTE.[StringA]
DECLARE @strA varchar(50) = '055367911126753316'
DECLARE @strB varchar(50) = '00055367'
SET @strA=(SELECT REVERSE(SUBSTRING(@strA, PATINDEX('%[^0 ]%', @strA + ' '), LEN(@strA))))
SET @strB=(SELECT REVERSE(SUBSTRING(@strB, PATINDEX('%[^0 ]%', @strB + ' '), LEN(@strB))))
SELECT @strA String ,@strB StringtoSearch,REVERSE(SUBSTRING(@strA,0,CHARINDEX(RIGHT(@strA,LEN(@strB)),@strA)))ExpectedOutput
Output
ExpectedOutput
---------------
911126753316