CODEWITHSUNDEEP
sqlfieldmatchpartial
Chris Jackson
Chris Jackson

Reputation: 11

Using WHERE for partial field match - How

Hello this is my first post and I am fairly new to SQL. Really what I am trying to find is an equivilent to MID$ where I can make updates based on part of a field.

    UPDATE OurTable
 SET UpdateField='Text-2008-001-old'
  WHERE '2008' like ('%' || UpdateField) AND KeyField='1'

In other words I wand to update to 'Text-2008-001-old' where the field might currently be 'Line-2008-000-000'

The position of the 2008 does not change within the existing data so really I just need to update and fields that contain 2008 where the KeyField is '1'

If there is a good online resource for SQL syntax that I have not found yet please feel free to point me there.

Hope I have explained this OK and thanks in advance for all suggestions.

Upvotes: 1

Views: 878

Answers (3)

Chains
Chains

Reputation: 13167

There are a number of wildcards to use with the LIKE operator (%,_,[],[^], etc.) -- explained here: http://msdn.microsoft.com/en-us/library/ms179859.aspx.

There is also the substring() function -- explained here: http://msdn.microsoft.com/en-us/library/ms187748.aspx, that is kind of like a MID function in other languages. (WHERE...LIKE SUBSTRING(...))

Upvotes: 0

luckystars
luckystars

Reputation: 1754

UPDATE OurTable SET UpdateField='Text-2008-001-old' WHERE UpdateField like 'Line-2008%' AND KeyField='1'

Upvotes: 0

sgeddes
sgeddes

Reputation: 62851

I think you just need to use LIKE:

UPDATE OurTable
SET UpdateField='Text-2008-001-old'
WHERE UpdateField LIKE '%2008%' AND KeyField='1'

Or if there are always dashes around it, perhaps LIKE '%-2008-%' would be better.

Good luck.

Upvotes: 1

Related Questions