Sql String Manipulation [Get All Text Left of '(']

SQL string manipulation [Get all text left of '(']

I think you've just put a wrong character

case
    when CHARINDEX('(', SourceOfBooking) > 0 then
        rtrim(left(SourceOfBooking, CHARINDEX('(', SourceOfBooking) - 1))
    else
        SourceOfBooking
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);

Extract string from a text after a keyword

Here is an example using SUBSTRING():

SELECT SUBSTRING(YourField, CHARINDEX(Keyword,YourField) + LEN(Keyword), LEN(YourField))

Another example:

declare @YourField varchar(200) = 'Mary had a little lamb'
declare @Keyword varchar(200) = 'had'
select SUBSTRING(@YourField,charindex(@Keyword,@YourField) + LEN(@Keyword), LEN(@YourField) )

Result:

 a little lamb

Please note that there is a space before the 'a' in this string.

How to get everything left of a certain set of characters in BigQuery SQL?

#standardSQL
SELECT 
  str, 
  REGEXP_REPLACE(str, r'MIN.*', '') option_1,
  REGEXP_EXTRACT(str, r'(.*?)MIN.*') option_2
FROM `project.dataset.table`

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 text after second occurrence of a specific character

Call the CHARINDEX function twice:

SELECT SUBSTRING(
           code,
           NULLIF(CHARINDEX('_', code, NULLIF(CHARINDEX('_', code), 0) + 1), 0) + 1,
           LEN(code)
       )
FROM (VALUES
    ('a_b_c'),
    ('a_b')
) x(code)

---

Related Topics