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
Sql Query to Get Number of Times a Field Repeats for Another Specific Field
Postgres: Insert If Does Not Exist Already
How to Get Textual Contents from Blob in Oracle SQL
Select Only Rows With Max Date
Selecting Same Column Twice from a Single Table But With Different Conditions
Is There a Command to Test an SQL Query Without Executing It ( MySQL or Ansi SQL )
T-Sql Challenges from Hackerrank
Convert String to Date in Ms Access Query
Subtraction Between Two SQL Queries
Multiple Insert With Same Column (Ms Access)
How to Calculate Percentage of Counts in SQL
Posgresql - Error: Relation "Table_Name" Does Not Exist. How to Query Without Schema Name
Sql Query to Get the Employee Name and Their Manager Name from the Same Table
How to Return a Flag If Exist Id in Another Table MySQL
Oracle SQL Hours Difference Between Dates in Hh:Mm:Ss
Query to Calculate Average of Employee Salaries Working Under Manager in SQL