trimming a text string

• Jeff Moden

  SSC Guru

  Points: 1004343

  More actions

  May 12, 2012 at 11:50 pm

  #1486838

  Ummmm.... if the first character is always a backslash (and I think it will be), you could really shorten up the code by starting the charindex search at character #2. 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• polkadot

  SSChampion

  Points: 13664

  More actions

  May 13, 2012 at 7:42 am

  #1486852

  you are correct. Thanks Jeff. It's still totally relevant (ie. not just an exercise). :kiss:

  --Quote me

• Jeff Moden

  SSC Guru

  Points: 1004343

  More actions

  May 13, 2012 at 9:30 am

  #1486857

  polkadot (5/13/2012)

  ---

  you are correct. Thanks Jeff. It's still totally relevant (ie. not just an exercise). :kiss:

  Perhaps this, then.

  --===== Create some test data

  SELECT d.ProdPath

  INTO #TestTable

  FROM (

  SELECT '\Beverages\Soda Pop\Mountain Dew' UNION ALL

  SELECT '\Beverages\Soda Pop' UNION ALL

  SELECT '\Beverages' UNION ALL

  SELECT '\\SomethingDifferent' UNION ALL

  SELECT 'Partial\Listing'

  ) d (ProdPath)

  ;

  --===== Possible simplified solution.

  -- Returns a NULL if there's no 2nd backslash anywhere after the 1st character.

  SELECT STUFF(SUBSTRING(ProdPath,NULLIF(CHARINDEX('\',ProdPath,2),0),8000),1,1,'')

  FROM #TestTable

  ;

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004343

  More actions

  May 13, 2012 at 9:38 am

  #1486858

  Ah... I just kind of assumed what you wanted if there were no second backslash. A simple addition to the code above will return the blank you want for that condition.

  --===== Create some test data

  SELECT d.ProdPath

  INTO #TestTable

  FROM (

  SELECT '\Beverages\Soda Pop\Mountain Dew' UNION ALL

  SELECT '\Beverages\Soda Pop' UNION ALL

  SELECT '\Beverages' UNION ALL

  SELECT '\\SomethingDifferent' UNION ALL

  SELECT 'Partial\Listing'

  ) d (ProdPath)

  ;

  --===== Possible simplified solution.

  -- Returns a BLANK if there's no backslash after the 2nd character.

  SELECT ISNULL(STUFF(SUBSTRING(ProdPath,NULLIF(CHARINDEX('\',ProdPath,2),0),8000),1,1,''),'')

  FROM #TestTable

  ;

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004343

  More actions

  May 13, 2012 at 9:42 am

  #1486860

  Just reread the problem again. If you really want to preserve the 2nd backslash, just remove the STUFF from the code above. Like this.

  SELECT d.ProdPath

  INTO #TestTable

  FROM (

  SELECT '\Beverages\Soda Pop\Mountain Dew' UNION ALL

  SELECT '\Beverages\Soda Pop' UNION ALL

  SELECT '\Beverages' UNION ALL

  SELECT '\\SomethingDifferent' UNION ALL

  SELECT 'Partial\Listing'

  ) d (ProdPath)

  ;

  --===== Possible simplified solution.

  -- Returns a BLANK if there's no backslash after the 2nd character.

  SELECT ISNULL(SUBSTRING(ProdPath,NULLIF(CHARINDEX('\',ProdPath,2),0),8000),'')

  FROM #TestTable

  ;

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• polkadot

  SSChampion

  Points: 13664

  More actions

  June 6, 2012 at 5:58 am

  #1497550

  Jeff, rather uncanny that you would know I might need to...."Return a BLANK if there's no backslash after the 2nd character".

  I needed this today. Thanks again.

  --Quote me

• Jeff Moden

  SSC Guru

  Points: 1004343

  More actions

  June 6, 2012 at 11:18 am

  #1497774

  It just seemed like a logical extension of your original request which got rid of everything up to and including the first baskslash.

  Thank you very much for the feedback. Glad to have helped.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• polkadot

  SSChampion

  Points: 13664

  More actions

  June 11, 2012 at 12:58 pm

  #1499445

  This time, I'm trying to get everything in front of the first backslash of a field name. The values in ProdPath will never begin with a backslash, and may have no backslashes, eg 'Beverages'. Regardless, I need 'Beverages' or the word before the first backslash.

  How do you trim this??

  I tried the following but it returns NULLs.

  SELECT

  SELECT CASE when CHARINDEX('/', ProdPath) > 0 then SUBSTRING(ProdPath, 0,PATINDEX('%\%',Left(ProdPath,DATALENGTH(ProdPath))))

  end as Application

  From #TestTable

  SELECT d.ProdPath

  INTO #TestTable

  FROM (

  SELECT 'Beverages\Soda Pop\Mountain Dew' UNION ALL

  SELECT 'Beverages\Soda Pop' UNION ALL

  SELECT 'Beverages' UNION ALL

  SELECT 'SomethingDifferent' UNION ALL

  SELECT 'Partial\Listing'

  ) d (ProdPath)

  ;

  Thank you.

  --Quote me

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 11, 2012 at 1:13 pm

  #1499455

  First, take a CLOSE look at your CHARINDEX in your case statement. Second, you need an ELSE in your case statement.

  Try the following:

  SELECT d.ProdPath

  INTO #TestTable

  FROM (

  SELECT 'Beverages\Soda Pop\Mountain Dew' UNION ALL

  SELECT 'Beverages\Soda Pop' UNION ALL

  SELECT 'Beverages' UNION ALL

  SELECT 'SomethingDifferent' UNION ALL

  SELECT 'Partial\Listing'

  ) d (ProdPath)

  ;

  SELECT

  CASE when CHARINDEX('\', ProdPath) > 0

  then LEFT(ProdPath, CHARINDEX('\', ProdPath) - 1)

  ELSE ProdPath

  end as Application

  From

  #TestTable

  GO

  DROP TABLE #TestTable;

  GO

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  June 11, 2012 at 1:17 pm

  #1499457

  this , may be?

  ; with cte as

  (

  Select ProdPath = t.ProdPath + '\'

  from #TestTable t

  )

  select SUBSTRING(c.ProdPath , 1, CHARINDEX('\',ProdPath)-1)

  from cte c

• polkadot

  SSChampion

  Points: 13664

  More actions

  June 11, 2012 at 1:31 pm

  #1499471

  No, I am getting error:

  Msg 537, Level 16, State 2, Line 1

  Invalid length parameter passed to the LEFT or SUBSTRING function.

  even if I go

  SELECT CASE when CHARINDEX('/', ProdPath) > 0 then SUBSTRING(ProdPath , 1, CHARINDEX('\',ProdPath)-1)

  end as Application

  I get NULLs

  --Quote me

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 11, 2012 at 1:34 pm

  #1499474

  polkadot (6/11/2012)

  ---

  No, I am getting error:

  Msg 537, Level 16, State 2, Line 1

  Invalid length parameter passed to the LEFT or SUBSTRING function.

  even if I go

  SELECT CASE when CHARINDEX('/', ProdPath) > 0 then SUBSTRING(ProdPath , 1, CHARINDEX('\',ProdPath)-1)

  end as Application

  I get NULLs

  Have you tried my code? It ran just fine on my system.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 11, 2012 at 1:36 pm

  #1499475

  polkadot (6/11/2012)

  ---

  No, I am getting error:

  Msg 537, Level 16, State 2, Line 1

  Invalid length parameter passed to the LEFT or SUBSTRING function.

  even if I go

  SELECT CASE when CHARINDEX('/', ProdPath) > 0 then SUBSTRING(ProdPath , 1, CHARINDEX('\',ProdPath)-1)

  end as Application

  I get NULLs

  Look HERE ------------------| Do you notice anything? Also, no ELSE means if the THEN is false you get NULL returned.

  SELECT CASE when CHARINDEX('/', ProdPath) > 0 then SUBSTRING(ProdPath , 1, CHARINDEX('\',ProdPath)-1)

  end as Application

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 11, 2012 at 1:53 pm

  #1499483

  ColdCoffee (6/11/2012)

  ---

  this , may be?

  ; with cte as

  (

  Select ProdPath = t.ProdPath + '\'

  from #TestTable t

  )

  select SUBSTRING(c.ProdPath , 1, CHARINDEX('\',ProdPath)-1)

  from cte c

  Starting with what ColdCoffee did above, I came up with this:

  SELECT d.ProdPath

  INTO #TestTable

  FROM (

  SELECT 'Beverages\Soda Pop\Mountain Dew' UNION ALL

  SELECT 'Beverages\Soda Pop' UNION ALL

  SELECT 'Beverages' UNION ALL

  SELECT 'SomethingDifferent' UNION ALL

  SELECT 'Partial\Listing'

  ) d (ProdPath);

  SELECT

  LEFT(ProdPath, CHARINDEX('\', ProdPath + '\') - 1) as Application

  From

  #TestTable

  GO

  DROP TABLE #TestTable;

  GO

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 11, 2012 at 2:14 pm

  #1499501

  Just an FYI regarding the code above, it won't work if ProdPath is null, unless NULL is a valid return value.