Get everything after and before certain character in SQL Server

Question

I got the following entry in my database   images test jpg   I want to trim the entry so I get  test  So basically  I want everything after   and before    How can I solve it

User · Answer

You can try this   Declare  test varchar 100   images test jpg  Select REPLACE RIGHT  test charindex     reverse  test  -1    jpg

User · Answer

SELECT SUBSTRING  ravi1234 gmail com  1  CHARINDEX      ravi1234 gmail com  -1   Before  RIGHT  ravi123 gmail com   CHARINDEX      ravi123 gmail com   1   After

User · Answer

Before  SELECT SUBSTRING ParentBGBU 0 CHARINDEX     ParentBGBU 0   FROM dbo tblHCMMaster    After  SELECT SUBSTRING ParentBGBU CHARINDEX  -  ParentBGBU  1 LEN ParentBGBU   FROM dbo tblHCMMaster

User · Answer

I got some invalid length errors  So i made this function  this should not give any length problems  Also when you do not find the searched text it will return a NULL   CREATE FUNCTION  FN   SearchTextGetBetweenStartAndStop   string varchar max   SearchStringToStart varchar max   SearchStringToStop varchar max    RETURNS varchar max   BEGIN       SET  string      CASE                           WHEN CHARINDEX  SearchStringToStart  string    0                            OR CHARINDEX  SearchStringToStop RIGHT  string LEN  string  - CHARINDEX  SearchStringToStart  string    1 - LEN  SearchStringToStart      0                          THEN NULL                          ELSE SUBSTRING  string                                         CHARINDEX  SearchStringToStart  string    LEN  SearchStringToStart    1                                          CHARINDEX  SearchStringToStop RIGHT  string LEN  string  - CHARINDEX  SearchStringToStart  string    1 - LEN  SearchStringToStart   -2                                                                     END       RETURN  string  END

User · Answer

----select characters before   including    select SUBSTRING   abcde wxyz  0 CHARINDEX      abcde wxyz   1    --select characters after   including    select SUBSTRING  abcde wxyz  CHARINDEX      abcde wxyz   LEN  abcde wxyz

User · Answer

I found Royi Namir s answer useful but expanded upon it to create it as a function  I renamed the variables to what made sense to me but you can translate them back easily enough  if desired   Also  the code in Royi s answer already handled the case where the character being searched from does not exist  it starts from the beginning of the string   but I wanted to also handle cases where the character that is being searched to does not exist   In that case it acts in a similar manner by starting from the searched from character and returning the rest of the characters to the end of the string   CREATE FUNCTION  dbo   getValueBetweenTwoStrings   inputString  NVARCHAR 4000    stringToSearchFrom NVARCHAR 4000    stringToSearchTo  NVARCHAR 4000   RETURNS NVARCHAR 4000  AS BEGIN       DECLARE  retVal NVARCHAR 4000  DECLARE  stringToSearchFromSearchPattern NVARCHAR 4000            stringToSearchFrom        SELECT  retVal   SUBSTRING           inputString         PATINDEX  stringToSearchFromSearchPattern   inputString    LEN  stringToSearchFrom           CASE             CHARINDEX                   stringToSearchTo                   inputString                  PATINDEX  stringToSearchFromSearchPattern   inputString    LEN  stringToSearchFrom           WHEN             0         THEN             LEN  inputString    1         ELSE             CHARINDEX                   stringToSearchTo                   inputString                  PATINDEX  stringToSearchFromSearchPattern   inputString    LEN  stringToSearchFrom           END  -  PATINDEX  stringToSearchFromSearchPattern   inputString    LEN  stringToSearchFrom        RETURN  retVal END   Usage   SELECT dbo getValueBetweenTwoStrings  images test jpg           AS MyResult

User · Answer

Simply Try With LEFT  RIGHT  CHARINDEX   select  LEFT  RIGHT a name   CHARINDEX      name   1      CHARINDEX       RIGHT a name                          CHARINDEX      name   1     -1   splitstring  a name       from      select  images test jpg  as name a

User · Answer

I know this has been a while   but here is an idea  declare  test varchar 25     images test jpg   select   test as column name    parsename replace  test          1  as jpg   parsename replace  test          2  as test    parsename replace  test          3  as images

User · Answer

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

User · Answer

use the following function  left  test  charindex       test  - 1

User · Answer

I have made a method which is much more general      so     DECLARE  a NVARCHAR MAX   images test jpg      --Touch here DECLARE  keysValueToSearch NVARCHAR 4000        DECLARE  untilThisCharAppears NVARCHAR 4000        DECLARE  keysValueToSearchPattern NVARCHAR 4000           keysValueToSearch          --Nothing to touch here      SELECT SUBSTRING              a             PATINDEX  keysValueToSearchPattern   a    LEN  keysValueToSearch              CHARINDEX                  untilThisCharAppears                  a                 PATINDEX  keysValueToSearchPattern   a    LEN  keysValueToSearch               - PATINDEX  keysValueToSearchPattern   a    LEN  keysValueToSearch

User · Answer

Inspired by the work of Josien  I wondered about a simplification   Would this also work  Much shorter   SELECT SUBSTRING col  CHARINDEX       col    1  CHARINDEX       col  - CHARINDEX       col  - 1      I can t test right now because of right issues at my company SQL server  which is a problem in its own right

User · Answer

if Input  pg102a-wlc01s png intel com and Output should be pg102a-wlc01s we can use below query   select Substring pc name 0 charindex     pc name 0   pc name from tbl name pc

User · Answer

I just did this in one of my reports and it was very simple    Try this    MID Fields  Value 8 4    Note  This worked for me because the value I was trying to get was a constant not sure it what you are trying to get is a constant as well

User · Answer

Below query gives you data before  -  Ex- W12345A-4S  SELECT SUBSTRING Column Name 0  CHARINDEX  -  Column Name    as  new name  from  abc     Output - W12345A

User · Answer

declare  T table       Col varchar 20           insert into  T    Select  images test1 jpg    union all   Select  images test2 png    union all   Select  images test3 jpg    union all   Select  images test4 jpeg    union all   Select  images test5 jpeg    Select substring  LEFT Col charindex     Col -1  charindex     Col  1 len LEFT Col charindex     Col -1  -1   from  T

[sql] Get everything after and before certain character in SQL Server

Examples related to sql

Examples related to sql-server