Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Matching partial field value for update InStr
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

5 Posts

Posted - 02/06/2013 :  13:22:35  Show Profile  Reply with Quote
Hello this is my second post and I am fairly new to SQL. Really what I am trying to find is an equivilent to MID$ or InStr from VBA where I can make updates based on part match of a field.

I cobled together the code below from net searches but alas it didn't work.

    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.


5072 Posts

Posted - 02/06/2013 :  17:13:44  Show Profile  Visit russell's Homepage  Reply with Quote
Have a look at REPLACE()
Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 02/06/2013 :  22:55:14  Show Profile  Reply with Quote
The equivalent clauses in SQL are SUBSTRING for Mid$ and PATINDEX/CHARINDEX for Instr

but for your requirement, easiest solution might be to use REPLACE() if format is consistent

SQL Server MVP

Go to Top of Page

Starting Member

5 Posts

Posted - 02/07/2013 :  04:25:28  Show Profile  Reply with Quote
Thanks, I found the syntax below worked perfectly for my needs but will read up on the other clauses.

WHERE UpdateField LIKE '%2008%' AND KeyField='1'

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000