Find the exact word in string within case statement in sql.

Question

I am required to find or check that a specific word exists in string or not.

Suppose I have to find the word 'st' than I need the result true if and only if the following occurrences are there.

1. 'St is valid;'    -> true

2. 'DOB is valid;ST is invalid;'    -> true

3. 'DOB is valid; ST is invalid;'    -> true

4. 'DOB is valid;invalid ST;'    -> true

5. 'DOB is valid; invalid ST;'    -> true

6. 'DOB is valid; invalid STate;'    -> false

Means the exact ST should be search. It is  not free text search.

T-SQL is needed to be used in select statement with case using PATINDEX, RegEx or any suitable t-sql command.

Its urgent please help me.

Thanks

Answer 1

Try

declare @t table (Phrase varchar(max), id int identity(1,1) primary key)
insert into @t (phrase) values 
('St is valid;'),('DOB is valid;ST is invalid;'),
('DOB is valid; ST is invalid;'), ('DOB is valid;invalid ST;'), ('DOB is valid; invalid ST;'),('DOB is valid; invalid STate;')

select * from @t where ' ' + Phrase + ' ' like '%[ ;]St[ ;]%'

Assuming only space and ; characters. You can add comma and . if needed.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 2

Try

declare @t table (Phrase varchar(max), id int identity(1,1) primary key)
insert into @t (phrase) values 
('St is valid;'),('DOB is valid;ST is invalid;'),
('DOB is valid; ST is invalid;'), ('DOB is valid;invalid ST;'), ('DOB is valid; invalid ST;'),('DOB is valid; invalid STate;')

select * from @t where ' ' + Phrase + ' ' like '%[ ;]St[ ;]%'

Assuming only space and ; characters. You can add comma and . if needed.

---

According to the sample, I think that the below script is sufficient:

SELECT  * FROM  @t WHERE  Phrase like '%St[ ;]%'

---

Please click "Mark As Answer" if my post helped.

Answer 3

where Phrase  like '%[^a-z]St[^a-z]%'

Answer 4

This actually eliminated the first entry that started with St. Unfortunately, there is no way that I know of in SQL to allow for any character in the range or no character at all. So, to use your idea we would need to add or Phrase like 'St[^a-z0-9]%'

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 5

This would not work for the "first street"

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 6

Hi,

Full-Text Search can be the best option in this case. the "ST" would be indexed individually and you have no need to consider any leading or appending non-alphabet character.

use TestDB;
go
 
create table TestFTS (
Id int not null,
TestText nvarchar(2000)
)

create unique index test_tfts on TestFTS(Id);
create fulltext catalog ftcat_tfts;
create fulltext index on TestFTS(TestText)
key index test_tfts on ftcat_tfts
with change_tracking auto, stoplist off
go 

insert into TestFTS values(1,'St is valid;' );
insert into TestFTS VALUES(2,'DOB is valid;ST is invalid;');
insert into TestFTS VALUES(3,'DOB is valid; ST is invalid;');
insert into TestFTS VALUES(4,'DOB is valid;invalid ST;' );
insert into TestFTS VALUES(5,'DOB is valid; invalid ST;' );
insert into TestFTS VALUES(6,'DOB is valid; invalid STate;' ); 
GO
 
SELECT *, CASE WHEN CONTAINS(TestText,'ST') THEN 'TRUE' ELSE 'FALSE' END FROM TestFTS
 
GO

If you have any question, feel free to let me know.

---

Eric Zhang
TechNet Community Support

Answer 7

Hi,

Full-Text Search can be the best option in this case. the "ST" would be indexed individually and you have no need to consider any leading or appending non-alphabet character.

use TestDB;
go
 
create table TestFTS (
Id int not null,
TestText nvarchar(2000)
)

create unique index test_tfts on TestFTS(Id);
create fulltext catalog ftcat_tfts;
create fulltext index on TestFTS(TestText)
key index test_tfts on ftcat_tfts
with change_tracking auto, stoplist off
go 

insert into TestFTS values(1,'St is valid;' );
insert into TestFTS VALUES(2,'DOB is valid;ST is invalid;');
insert into TestFTS VALUES(3,'DOB is valid; ST is invalid;');
insert into TestFTS VALUES(4,'DOB is valid;invalid ST;' );
insert into TestFTS VALUES(5,'DOB is valid; invalid ST;' );
insert into TestFTS VALUES(6,'DOB is valid; invalid STate;' ); 
GO
 
SELECT *, CASE WHEN CONTAINS(TestText,'ST') THEN 'TRUE' ELSE 'FALSE' END FROM TestFTS
 
GO

It will not work. This query returns true for the 6th street instead of false according to OP sample.

---

Please click "Mark As Answer" if my post helped.

Answer 8

With that sample code I got all FALSE.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 9

Hi Naomi,

The sample code returns as below in my environment.


The reason you got all FALSE can be any of the listed.

• I didn't specify the full text indexed column's language, so the default language of the SQL Server instance is used. In my case it is English, and full text index varies according to languages.
• It takes some time to index, so if you happen to run the sample code in a whole block, you would probably got all false for the index is not ready, try to run the last select statement after seconds.
• A stoplist containing "ST" is specified so that "ST" is not indexed in your scenario.

---

Eric Zhang
TechNet Community Support

Answer 10

I re-ran this statement now and got the same result as you, so your second theory is true.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles