Sql Select Everything After a Certain Character

SQL Select everything after character

You can use:

select right(col, charindex('-', reverse(col)) - 1)

SQL SELECT everything after a certain character

select SUBSTRING_INDEX(supplier_reference,'=',-1) from ps_product;

Please use this for further reference.

Select everything after a character in SQL Server

If all the string starts with X then you just can select substring starting from second character as below:

select substring('X1234',2,len('X1234'))

Or if there is chance to start with other characters then first you can use case when check whether the first character is X or not.

select case when left('X1234',1)='X' then substring('X1234',2,len('X1234'))end

Get everything after and before certain character in SQL Server

If you want to get this out of your table using SQL, take a look at the following functions that will help you: SUBSTRING and CHARINDEX. You can use those to trim your entries.

A possible query will look like this (where col is the name of the column that contains your image directories:

SELECT SUBSTRING(col, LEN(SUBSTRING(col, 0, LEN(col) - CHARINDEX ('/', col))) + 1, 
LEN(col) - LEN(SUBSTRING(col, 0, LEN(col) - CHARINDEX ('/', col))) - LEN(SUBSTRING(
col, CHARINDEX ('.', col), LEN(col))));

Bit of an ugly beast. It also depends on the standard format of 'dir/name.ext'.

Edit:

This one (inspired by praveen) is more generic and deals with extensions of different length:

SELECT SUBSTRING(col, LEN(LEFT(col, CHARINDEX ('/', col))) + 1, LEN(col) - LEN(LEFT(col, 
CHARINDEX ('/', col))) - LEN(RIGHT(col, LEN(col) - CHARINDEX ('.', col))) - 1);

SQL Select everything after numeric character

 SELECT SUBSTRING(LEFT('ABC XYZ 100 MG PL/2 ML ABCD',CHARINDEX('ML', 'ABC XYZ 100 MG PL/2 ML ABCD') + 2),PATINDEX('%[0-9]%','ABC XYZ 100 MG PL/2 ML ABCD'),LEN('ABC XYZ 100 MG PL/2 ML ABCD'))

-

SELECT SUBSTRING(LEFT(col,CHARINDEX('ML', col) + 2),PATINDEX('%[0-9]%',col),LEN(col))
from table

Although, you state in your question you want everything from "before" 'ML' and your expected output has 'ML' in it

Getting everything after last '/'

I would use an approach of finding how many characters you need to use from the right. I would do this by first reversing the string and then searching for the '/'. This will tell you how many characters from the right this '/' is. I would then use this in the RIGHT function:

SQL Fiddle

MS SQL Server 2017 Schema Setup:

Query 1:

DECLARE @documentName varchar(100) = 'Uploads/Documents/6093/12/695-Graco-SW_5-15-19.pdf'

SELECT RIGHT(@documentName, CHARINDEX('/',REVERSE(@documentName))-1)

Results:

|                          |
|--------------------------|
| 695-Graco-SW_5-15-19.pdf |

Get everything before a certain character in SQL

If you need the second -:

SELECT 
LEFT(STRING, CHARINDEX('-', @test, CHARINDEX('-', @test) + 1) -1) STRIPPED_STRING
FROM @TABLE

Explanation: CHARINDEX will get you the index of the - - doing it twice (+ 1) specifies that the outter CHARINDEX should start at the spot after the first - in the string.

If you want to chop off everything after the last - instead (regardless of whether it's second or not):

SELECT 
LEFT(STRING, LEN(STRING) - CHARINDEX('-', REVERSE(STRING))) STRIPPED_STRING
FROM @table

This time, you get the CHARINDEX of the last (reverse the string) -, and subtract that from the length of the whole string.



Related Topics



Leave a reply



Submit