This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C1AAA8.9DC9D584
Content-Type: text/plain

I split it into 2 queries and added the where clause in case you had files
that didn't have an extension. eg MyFile or AnotherFile . This would return
0 from CHARINDEX, which when you subtracted 1 from it made for an invalid
LEFT statement. I didn't stop to look at the documentation for PATINDEX, but
I suspected that the error was due to a file name without an extension. So I
added in the where clause. Also, I assumed that you wanted to return file
names even if they didn't have an extension, so I UNIONed the query to
another that would return files which didn't have extensions.

You might get one further error, if files have a name like: MyFile.doc.ext,
you will return MyFile rather than myFile.doc. To be really rigorous, if you
using SQL2k you could write a function to strip off the extension. It would
need to iterate through the string until it got to the last '.' in the
string. Then cut off everything from the '.' onwards.

regards
David Cameron
nOw.b2b
dcameron@i...

-----Original Message-----
From: howard@c... [mailto:howard@c...]
Sent: Friday, 1 February 2002 8:41 AM
To: sql language
Subject: [sql_language] RE: Selecting Part of a Database Field to update
a- nother


Thanks Dave...

Why do I need the WHERE clause to make work...I actually wanted to update 
which I have done...

UPDATE ShelbyDeeds SET DeedNumber = LEFT(DeedFileName,CHARINDEX('.', 
DeedFileName) - 1)
WHERE CHARINDEX('.', DeedFileName) <> 0


$subst('Email.Unsub').