How to remove last part form string form one column in sql server query ?

Question

0.00/5 (No votes)

See more:

Hi
How to remove last part of word form string in sql server.

For E.g this is string in one column "Viewer\DWG MPP msg EML\New folder\1343939490.pdf"
i need to get word upto "Viewer\DWG MPP msg EML\New folder" and omit\remove last part form this word "\1343939490.pdf" can use split.i mean need to remove last slash and file name(\1343939490.pdf)

Note :i am going to use this query in where condition as sub query.so i don't want in store procedure and that slash("\") also increase or decrease i mean string sometime contains

Viewer\DWG MPP msg EML\1343939490.pdf
Viewer\1343939490.pdf
Viewer\DWG MPP msg EML\New folder\DWG MPP msg EML\New folder\1343939490.pdf

Regards
Aravind

Posted 27-Dec-14 3:23am

Aravindba

Updated 6-Jul-23 22:11pm

Maciej Los

v4

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2014-12-27T04:01:00

Solution 1

SQL doesn't have a Split function (it has no concept of arrays, so it can't return a collection of values except as a table).

However, it does have a Reverse function:

SQL

SELECT LEFT(Location, LEN(Location) - CHARINDEX('\', REVERSE('\' + Location))) AS [Result] from MyTable

You can always create an SQL Function to do that if you want to be readable in queries.

Posted 27-Dec-14 4:01am

OriginalGriff

Comments

Maciej Los 27-Dec-14 10:48am

+5

Aravindba 28-Dec-14 6:20am

Thanks a lot,+5

OriginalGriff 28-Dec-14 6:31am

You're welcome!