SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

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

Lando88
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.

russell
Pyro-ma-ni-yak

USA
5037 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

visakh16
Very Important crosS Applying yaK Herder

India
47960 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
http://visakhm.blogspot.com/
Go to Top of Page

Lando88
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  
 New 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.05 seconds. Powered By: Snitz Forums 2000