如果输入字符串在表行中有子字符串，如何替换输入字符串中的字符串？ [英] How do I replace a string in the input string if input string has a substring in the table rows?

问题描述

我有一个列表，其值如下：



仅限行动

仅限新南威尔士州

仅限QLD



如果我现在传递一个字符串，例如ABC - QLD ONLY，那么我需要取回ABC作为结果（仅作为QLD） 在桌子旁边。

有谁可以帮我解决这个问题？



谢谢。



我尝试了什么：



我尝试创建一个函数，但没有成功。

I have a single column table with values as below:

ACT ONLY
NSW ONLY
QLD ONLY

If i now pass a string say, "ABC - QLD ONLY", then i need to get back "ABC" as a result (as "QLD ONLY" is in the table row).
Can anyone please help me solve this?

Thank you.

What I have tried:

I have tried creating a function, with no success.

推荐答案

try

<pre>

declare @table table ( col nvarchar(50) )

insert into @table values ('ACT ONLY')
insert into @table values ('NSW ONLY')
insert into @table values ('QLD ONLY')

declare @find  nvarchar(50)
declare @result  nvarchar(50)
set @find ='ABC - QLD ONLY'
set  @result =    ( select top 1 col from @table where @find like '%'+col+'%')
if(@result is not null )
begin
 select @result = REPLACE(@find , @result ,'') 
  select @result = REPLACE(@result , '-' ,'') 
end
select  @result -- ABC

这应该也工作

This should work too

DECLARE @temp TABLE (Col1 VARCHAR(50))
DECLARE @input VARCHAR(50)

SET @input = 'ABC - QLD ONLY'

INSERT INTO @temp
	SELECT 'ACT ONLY' UNION SELECT 'NSW ONLY' UNION SELECT 'QLD ONLY'

SELECT REPLACE(REPLACE(@input, col1, ''),' - ','') FROM @temp
WHERE CHARINDEX(col1,@input)  > 0

这篇关于如果输入字符串在表行中有子字符串，如何替换输入字符串中的字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！