CODEWITHSUNDEEP
sqlsql-server
Ryan Price
Ryan Price

Reputation: 21

SQL Find field in string with where clause using wildcards

I'm in need of some expert advice! Below is my table and the where clause I'm looking for. I need to be able to select all records that contain a current date, in the where clause below it would be anything in the month of July 2018. Unfortunately the data in my database is stored in a string for the ProjNotes field as you can see and cannot be changed.

Any help pointing me in the right direction will be greatly appreciated! 

DECLARE @Table AS TABLE (JCCo int, Job varchar(Max), Phase varchar(Max),ProjNotes varchar(MAX))

Insert Into @Table (JCCo, Job, Phase,ProjNotes)
Select 2,'2959-','20.11.100','11/13/2015 1:40:50 PM by CORDOM\ttest
Added 3 wks 9k to include time thru Dec. 2017.

11/17/2016 2:22:28 PM by COR_DOM\ttest
Added 4 months or 62k for a full time PM thru end of project May-2018.

1/4/2018 10:55:37 AM by COR_DOM\ttestd
Reduced proj. costs by approx. 65k due to PM Staff being consolidated.

7/11/2018 2:45:38 PM by COR_DOM\ttest
Increased proj. costs by approx. 36k due to new PM and previous PM who was the Ops Manager and that cost was being charged to general overhead.'

Select *
From @Table
Where ProjNotes Like ('%7/**/2018%')

Upvotes: 0

Views: 72

Answers (2)

Tracy Zhou
Tracy Zhou

Reputation: 734

Use underscore. The underscore represents a single character.

Select *
From @Table
Where ProjNotes Like ('%7/__/2018%') or ProjNotes Like ('%7/_/2018%')

If there is always a space before the date, use the following to take care of month January and February

Select *
From @Table
Where ProjNotes Like ('% 7/__/2018%') or ProjNotes Like ('% 7/_/2018%') or ProjNotes Like ('% _7/__/2018%') or ProjNotes Like ('% _7/_/2018%')

Upvotes: 3

Pawel Czapski
Pawel Czapski

Reputation: 1864

Try below should work fine, just slightly changed what @Tracy added, to handle 1 or 2 digit day:

DECLARE @Table AS TABLE (JCCo int, Job varchar(Max), Phase varchar(Max),ProjNotes varchar(MAX))

Insert Into @Table (JCCo, Job, Phase,ProjNotes)
Select 2,'2959-','20.11.100','11/13/2015 1:40:50 PM by CORDOM\ttest
Added 3 wks 9k to include time thru Dec. 2017.

11/17/2016 2:22:28 PM by COR_DOM\ttest
Added 4 months or 62k for a full time PM thru end of project May-2018.

1/4/2018 10:55:37 AM by COR_DOM\ttestd
Reduced proj. costs by approx. 65k due to PM Staff being consolidated.

7/11/2018 2:45:38 PM by COR_DOM\ttest
Increased proj. costs by approx. 36k due to new PM and previous PM who was the Ops Manager and that cost was being charged to general overhead.'
union
select 3,'2959-','20.11.100','blah blah 7/1/2018 blah blah '


Select *
From @Table
Where ProjNotes Like ('%7/__/2018%') 
    or ProjNotes Like ('%7/_/2018%')

Upvotes: 0

Related Questions