sql query problem

mateenmohd · June 17th, 2003, 05:43 AM

Hi all,

Please inform, I have a problem in query.
How I can select only image name from the table where record are save in SQL SERVER like this.

$(rootdir)\contract25\imagename25.tif
$(rootdir)\contract560\imagename1560.tif
$(rootdir)\contract-6156\imagename6156.tif

Different images are save in different directories. I need only image name ?
what function / method will use in query to select only imagename from the table ?
not select $ sign and subdirectory, only image name.?

Best regards.

Mateen Martin
[email protected]

pgtips · June 17th, 2003, 06:21 AM

Something like this will do it in SQL Server (just change ImageName and TableName to whatever they are in your db:

Code:

SELECT REVERSE(LEFT(REVERSE(ImageName), CHARINDEX('\', REVERSE(ImageName)) - 1))
FROM TableName

It look a bit cumbersome, but you need to get everything after the last \ and you don't know how many \'s there will be, so it seemed easier to me to:
- use REVERSE to swap the string around so the last \ becomes the first - take everything to the left of the first - use reverse again to put the image name back in the correct order

hth
Phil

pgtips · June 17th, 2003, 06:23 AM

That explanation got a bit mangled by the time it reached the forum, so I'll try again:

Code:

- use REVERSE to swap the string around so the last \ becomes the first - take everything to the left of the first - use reverse again to put the image name back in the correct order

pgtips · June 17th, 2003, 06:30 AM

Those carriage returns and \'s just keep disappearing. If you can't see how it works, just try pasting this bit of T-SQL into query analyzer and running it:

Code:

DECLARE @full varchar(255)
DECLARE @part varchar(255)
DECLARE @pos int
SET @full = '$(rootdir)\contract25\imagename25.tif'

SET @part = REVERSE(@full)
RAISERROR('REVERSE=%s', 10, 1, @part)
SET @pos = CHARINDEX('\', @part)
RAISERROR('CHARINDEX=%d', 10, 1, @pos)
SET @part = LEFT(@part, @pos - 1)
RAISERROR('LEFT=%s', 10, 1, @part)
SET @part = REVERSE(@part)
RAISERROR('another REVERSE=%s', 10, 1, @part)

mateenmohd · June 18th, 2003, 01:29 AM

Thanks for your response.
it is reverse the field contents.

I want that when I run query
"select imagereferece from tablename "

only imagename return from the table.
does not return $ sign and subdirectory.

in the table's field ie. imagereference all images are save like this.

$(rootdir)\contract-25\imagename.tif

Best regards.

Mateen Martin
[email protected]

Quote:

quote:Originally posted by pgtips
Those carriage returns and \'s just keep disappearing. If you can't see how it works, just try pasting this bit of T-SQL into query analyzer and running it:

Code:

DECLARE @full varchar(255)
DECLARE @part varchar(255)
DECLARE @pos int
SET @full = '$(rootdir)\contract25\imagename25.tif'

SET @part = REVERSE(@full)
RAISERROR('REVERSE=%s', 10, 1, @part)
SET @pos = CHARINDEX('\', @part)
RAISERROR('CHARINDEX=%d', 10, 1, @pos)
SET @part = LEFT(@part, @pos - 1)
RAISERROR('LEFT=%s', 10, 1, @part)
SET @part = REVERSE(@part)
RAISERROR('another REVERSE=%s', 10, 1, @part)

mateenmohd · June 18th, 2003, 06:39 AM

Thanks for your function.
Yes it is return the only imagename

How I can use this function in select query ?
I want to retrieve the record by the help of query.
Because I have thousand of records in sql server.
And I need only imagename
Table name is project
Field name is imagereference

Thanks for your cooperation.

Best regards.

Mateen Martin
[email protected]

Quote:

quote:Originally posted by pgtips
Those carriage returns and \'s just keep disappearing. If you can't see how it works, just try pasting this bit of T-SQL into query analyzer and running it:

Code:

DECLARE @full varchar(255)
DECLARE @part varchar(255)
DECLARE @pos int
SET @full = '$(rootdir)\contract25\imagename25.tif'

SET @part = REVERSE(@full)
RAISERROR('REVERSE=%s', 10, 1, @part)
SET @pos = CHARINDEX('\', @part)
RAISERROR('CHARINDEX=%d', 10, 1, @pos)
SET @part = LEFT(@part, @pos - 1)
RAISERROR('LEFT=%s', 10, 1, @part)
SET @part = REVERSE(@part)
RAISERROR('another REVERSE=%s', 10, 1, @part)

pgtips · June 18th, 2003, 06:55 AM

As I said before, just do:

Code:

SELECT REVERSE(LEFT(REVERSE(ImageName), CHARINDEX('\', REVERSE(ImageName)) - 1))
FROM TableName

the "function" was just supposed to illustrate how/why the above SELECT works.

rgds
Phil

mateenmohd · June 18th, 2003, 11:55 PM

Dear Phil

I run the query ie.

SELECT REVERSE(LEFT(REVERSE(imagereference), CHARINDEX('\', REVERSE(imagereference)) - 1)) FROM archive

But it give following error

" Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function. â

Please check the query. Where imagereference is a field name of table.

I may be miror mistake written the query.

best regards.

Mateen Martin

quote]Originally posted by pgtips
As I said before, just do:

Code:

SELECT REVERSE(LEFT(REVERSE(ImageName), CHARINDEX('\', REVERSE(ImageName)) - 1))
FROM TableName

the "function" was just supposed to illustrate how/why the above SELECT works.

rgds
Phil
[/quote]

mateenmohd · June 19th, 2003, 02:05 AM

Dear Phil

Thanks for your help.
My problem have solved.
Your Query was correct, only my records entry was wrong.
It is display the only imagename.

Best regards.

Mateen

Quote:

quote:Originally posted by pgtips
As I said before, just do:

Code:

SELECT REVERSE(LEFT(REVERSE(ImageName), CHARINDEX('\', REVERSE(ImageName)) - 1))
FROM TableName

the "function" was just supposed to illustrate how/why the above SELECT works.

rgds
Phil