Solved

Trim/Replace String

Posted on 2007-04-02
5
434 Views
Last Modified: 2011-10-03
How would I change:
\\MyServer\Optimum\Spindle\Spindle_DataBase\Pictures\Linked Pictures\173960 001.jpg

to this:
173960 001.jpg

adria
0
Comment
Question by:adraughn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 3

Accepted Solution

by:
allennyc earned 500 total points
ID: 18839701
Hi Adria,

select right('\\MyServer\Optimum\Spindle\Spindle_DataBase\Pictures\Linked Pictures\173960 001.jpg',
      PATINDEX('%\%', REVERSE('\\MyServer\Optimum\Spindle\Spindle_DataBase\Pictures\Linked Pictures\173960 001.jpg'))-1)
0
 
LVL 13

Author Comment

by:adraughn
ID: 18839831
some of the fields do not have a path, only 'N/A'

i am getting this error:

Msg 536, Level 16, State 2, Line 1
Invalid length parameter passed to the RIGHT function.
0
 
LVL 13

Author Comment

by:adraughn
ID: 18839834
this is what i have:

use [spindle test]
go
select (right(imagepath_1,
      PATINDEX('%\%', REVERSE(imagepath_1))-1)) as Filename_1,
(right(imagepath_2,
      PATINDEX('%\%', REVERSE(imagepath_2))-1)) as Filename_2,
(right(imagepath_3,
      PATINDEX('%\%', REVERSE(imagepath_3))-1)) as Filename_3

from tblTD_Pics
0
 
LVL 3

Expert Comment

by:allennyc
ID: 18839937
Let's try to run a CASE statement to filter out the 'N/A' before we run the RIGHT function.

Hope this runs without requiring too much cleanup:

use [spindle test]
go
select      Filename_1 =
                  CASE imagepath_1
                        when 'N/A' THEN 'N/A'
                        else (right(imagepath_1, PATINDEX('%\%', REVERSE(imagepath_1))-1))
                  END,
            Filename_2 =
                  CASE imagepath_2
                        when 'N/A' THEN 'N/A'
                        else (right(imagepath_2, PATINDEX('%\%', REVERSE(imagepath_2))-1))
                  END,
            Filename_3 =
                  CASE imagepath_3
                        when 'N/A' THEN 'N/A'
                        else (right(imagepath_3, PATINDEX('%\%', REVERSE(imagepath_3))-1))
                  END
from tblTD_Pics
0
 
LVL 13

Author Comment

by:adraughn
ID: 18839963
works great, thanks... :)
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question