get starting to last specific string valuesin sql server

• asrinu13

  Say Hey Kid

  Points: 709

  More actions

  June 5, 2018 at 4:35 am

  #406552

  Hi I have one doubt in sql server .

  how to get first position to right side specific character position.

  table : empfiles

  filename:

  ab_re_uk_u_20101001
  ax_by_us_19991001
  abc_20181002

  I want output like below:
  filename
  ab_re_uk_u
  ax_by_us
  abc

  I tried like below :
  select SUBSTRING(filename,1,CHARINDEX('2',filename) - 1) as filename from empfiles

  above query is not given expected result please tell me how to write query to achive this task in sql server .

• Thom A

  SSC Guru

  Points: 99130

  More actions

  June 5, 2018 at 5:51 am

  #1992944

  I wouldn't expect that value of give the correct result, your CHARINDEX would return the position of the first underscore after the 2nd character. As you're looking for the last one, you'd be better use using REVERSE, and then CHARINDEX:
  WITH VTE AS(
    SELECT *
    FROM (VALUES('ab_re_uk_u_20101001'),('ax_by_us_19991001'),('abc_20181002')) V([filename]))
SELECT LEFT([filename], LEN([filename]) - CHARINDEX('_',REVERSE([filename])))
FROM VTE;


  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Phil Parkin

  SSC Guru

  Points: 246959

  More actions

  June 5, 2018 at 10:27 am

  #1992976

  Thom A - Tuesday, June 5, 2018 5:51 AM

  I wouldn't expect that value of give the correct result, your CHARINDEX would return the position of the first underscore after the 2nd character. As you're looking for the last one, you'd be better use using REVERSE, and then CHARINDEX:
  WITH VTE AS(
    SELECT *
    FROM (VALUES('ab_re_uk_u_20101001'),('ax_by_us_19991001'),('abc_20181002')) V([filename]))
SELECT LEFT([filename], LEN([filename]) - CHARINDEX('_',REVERSE([filename])))
FROM VTE;


  Or maybe even as simple as this, if the ending is always _YYYYMMDD:

  WITH vte2
AS
(
  SELECT *
  FROM
    (
     VALUES
       (
        'ab_re_uk_u_20101001'
       )
     ,    (
          'ax_by_us_19991001'
         )
     ,    (
          'abc_20181002'
         )
    ) V (filename)
)
SELECT LEFT(vte2.filename, LEN(vte2.filename)-9)
FROM vte2;

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• ranitb

  Right there with Babe

  Points: 741

  More actions

  June 20, 2018 at 6:15 am

  #1994607

  Something like this ?
  ;with t(txt) as (
    select 'ab_re_uk_u_20101001' union all
    select 'ax_by_us_19991001' union all
    select 'abc_20181002'
)
select    txt,
        REVERSE(txt) tmp1,
        CHARINDEX('_',REVERSE(txt)) tmp2,
        LEN(txt) tmp3,
        right(REVERSE(txt), len(txt)-CHARINDEX('_',REVERSE(txt))) tmp4,
        reverse(right(REVERSE(txt), len(txt)-CHARINDEX('_',REVERSE(txt)))) this_s_your_answer
from t

  O/p -
  

  txt	tmp1	tmp2	tmp3	tmp4	this_s_your_answer
  ab_re_uk_u_20101001	10010102_u_ku_er_ba	9	19	u_ku_er_ba	ab_re_uk_u
  ax_by_us_19991001	10019991_su_yb_xa	9	17	su_yb_xa	ax_by_us
  abc_20181002	20018102_cba	9	12	cba	abc

• SGT_squeequal

  SSCertifiable

  Points: 7167

  More actions

  June 20, 2018 at 6:23 am

  #1994613

  create table Q (col varchar(20))

  insert into q select 'ab_re_uk_u_20101001' union all
  select 'ax_by_us_19991001' union all
  select 'abc_20181002'

  select *,substring(col,0,patindex('%[0-9]%',col)-1) from q

  ***The first step is always the hardest *******

• Phil Parkin

  SSC Guru

  Points: 246959

  More actions

  June 20, 2018 at 6:30 am

  #1994615

  ranitb - Wednesday, June 20, 2018 6:15 AM

  Something like this ?
  ;with t(txt) as (
    select 'ab_re_uk_u_20101001' union all
    select 'ax_by_us_19991001' union all
    select 'abc_20181002'
)
select    txt,
        REVERSE(txt) tmp1,
        CHARINDEX('_',REVERSE(txt)) tmp2,
        LEN(txt) tmp3,
        right(REVERSE(txt), len(txt)-CHARINDEX('_',REVERSE(txt))) tmp4,
        reverse(right(REVERSE(txt), len(txt)-CHARINDEX('_',REVERSE(txt)))) this_s_your_answer
from t

  O/p -
  

  txt	tmp1	tmp2	tmp3	tmp4	this_s_your_answer
  ab_re_uk_u_20101001	10010102_u_ku_er_ba	9	19	u_ku_er_ba	ab_re_uk_u
  ax_by_us_19991001	10019991_su_yb_xa	9	17	su_yb_xa	ax_by_us
  abc_20181002	20018102_cba	9	12	cba	abc

  REVERSE() is an expensive operation and your code is way more complex than the other suggestions! Would be nice to hear back from the OP to see what they ended up using.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• ranitb

  Right there with Babe

  Points: 741

  More actions

  June 20, 2018 at 6:48 am

  #1994622

  Phil Parkin - Wednesday, June 20, 2018 6:30 AM

  REVERSE() is an expensive operation and your code is way more complex than the other suggestions! Would be nice to hear back from the OP to see what they ended up using.

  Hi Phil,

  Yes, that's true - it's lot expensive. I was just playing around, but found much better responses already posted. 🙂