Get Everything After and Before Certain Character in SQL Server

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 character

You can use:

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

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.

Get name after and before certain character in SQL Server

If you are making use of an older version of SQL server which doenst support string_split. The reverse function comes in handy as follows.

The steps i do is reverse the string, grab the char position of ".", grab the char position of "\" then apply the substring function on it to slice the data between the two positions. Finally i reverse it again to get the proper value.

Here is an example

with data
  as(select '\\folder.abc\es\Folder-A\\2020-08-03\namefile.csv' as col
    )
select reverse(substring(reverse(col)
                ,charindex('.',reverse(col))+1
                ,charindex('\',reverse(col))
                 -
                 charindex('.',reverse(col))-1
                 )
               ) as file_name
  from data    

+-----------+
| file_name |
+-----------+
| namefile  |
+-----------+

dbfiddle link

https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=8c0fc11f5ec813671228c362f5375126

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 substring after and before '-' character

One option is ParseName()

Example

Declare @S varchar(max)='AM-65-800'

Select parsename(replace(@S,'-','.'),2)

Returns

65

Get everything after a string pattern and before a ' ' in Databricks SQL

You have tagged this question as both sql-server and databricks. Based on your use of length() instead of len(), I assume that you are using databricks. In that case, you can make use of the regexp_extract() function

Try: "regexp_extract(properties_desc, '(?<=cardType=)[^ ]*')".

This is untested, as I am not a databricks programmer.

The "[^ ]*" in the above will match and extract a string of non-space characters after "cardType=". The "(?<=...)" is a "look-behind" construct that requires that the matched text be preceded by "cardType=", but does not include that text in the result. The end result is that the regex matches and extracts everything after "cardtype=" up to the next space (or the end of the string).

Regular expressions are a pretty powerful string matching tool. Well worth learning if you are not already familiar with them. (I wish SQL Server had them.)

---

Related Topics