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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Matching partial field value for update InStr

Author  Topic 

Lando88
Starting Member

5 Posts

Posted - 2013-02-06 : 13:22:35
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

5072 Posts

Posted - 2013-02-06 : 17:13:44
Have a look at REPLACE()
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-06 : 22:55:14
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 - 2013-02-07 : 04:25:28
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
   

- Advertisement -