Wildcard in Query? - SQL Server Forums

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

Wildcard in Query?

Author

Topic

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-12-11 : 02:06:46

I want to update data only where the value of the 'image_path' column is NOT = 192.168.150.12/images/*

Im basically trying to exclude creating duplicates, where this path already exists.

Here is my code:


INSERT INTO IMAGE (FCN, IMAGE_NAME2)
SELECT FCN, Col066
FROM GRAB where Col066 <> '        '
update IMAGE
Set PERIMAGE_PATH = 'http://192.168.150.12/images/' +IMAGE_NAME2+ '.jpg' 
FROM IMAGE WHERE image_name2 IS NOT NULL and perimage_path is NOT = 192.168.150.12/images/*

What is the proper code to do this. I know the last line does not work. Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-11 : 02:27:09

update IMAGE
Set PERIMAGE_PATH = 'http://192.168.150.12/images/' +IMAGE_NAME2+ '.jpg'
FROM IMAGE WHERE image_name2 IS NOT NULL and perimage_path not like '%192.168.150.12/images/*%'

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 04:01:55

[code]INSERT IMAGE
(
FCN,
IMAGE_NAME2
)
SELECT FCN,
Col066
FROM GRAB
WHERE Col066 > ''

UPDATE IMAGE
SET PERIMAGE_PATH = 'http://192.168.150.12/images/' +IMAGE_NAME2+ '.jpg'
WHERE IMAGE_NAME2 IS NOT NULL
AND perimage_path NOT LIKE '192.168.150.12/images/%'[/code]

E 12°55'05.25"
N 56°04'39.16"

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-12-11 : 05:09:33

Or.......
If like me you hate using LIKE'%%' comparisons - then something like this.

UPDATE IMAGE
SET PERIMAGE_PATH = 'http://192.168.150.12/images/' +IMAGE_NAME2+ '.jpg'
WHERE IMAGE_NAME2 IS NOT NULL
AND COALESCE(CHARINDEX('192.168.150.12/images/', perimage_path), 0) = 0

Duane.

edit: handle nulls too

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-12-11 : 11:19:36

If I run this twice, I create duplicates in the database. Im trying to avoid that.

UPDATE IMAGE
SET PERIMAGE_PATH = 'http://192.168.150.12/images/' +IMAGE_NAME2+ '.jpg'
WHERE IMAGE_NAME2 IS NOT NULL
AND perimage_path NOT LIKE '192.168.150.12/images/%'

Why is it creating duplicates? How can I avoid that?

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-12-11 : 12:21:32

So basically I need to add lines to the opening INSERT statement.

INSERT INTO IMAGE (FCN, IMAGE_NAME2)
SELECT FCN, Col066
FROM GRAB where Col066 <> ' '

How can I specify this INSERT statement to run ONLY when value of column 'Col066' in the GRAB table does NOT exist as a value in the column 'IMAGE_NAME2' in the IMAGE table.

I believe its a JOIN, but I dont know the syntax.... help? Thanks

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-12-11 : 13:25:23

Here is the answer:

FROM GRAB g where Col066 <> ' '
and not exists (select * from IMAGE n where g.Col066 = n.IMAGE_NAME2)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-11 : 13:57:18

quote:
Originally posted by ditch

Or.......
If like me you hate using LIKE'%%' comparisons - then something like this.

Why is that? Do you not like your quries to use indexes?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources