Solved

Trim/Replace String

Posted on 2007-04-02
5
427 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
  • 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQl server restarts itself 6 38
Query to Add Late Tolerance 10 68
Anyway to make these 2 SQL statements into one? 13 39
View SQL 2005 Job package 16 43
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

810 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