Substring Question

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  October 30, 2017 at 2:59 pm

  #1965864

  Alan.B - Monday, October 30, 2017 2:02 PM

  Another way, using Luis' sample data

  SELECT name, rtrim(ltrim(isnull(parsename(' '+x.name+' ', 2), x.name))) as name2
FROM (VALUES('Luis.Cazares'),('Luis Cazares'),('Luis Cazares.'),('.Luis Cazares')) x(name);

  Again, I strongly recommend that concatenation be avoided for such things.  See my previous response to Luis.

  --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)
  

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  October 30, 2017 at 3:34 pm

  #1965867

  Jeff Moden - Monday, October 30, 2017 2:59 PM

  Alan.B - Monday, October 30, 2017 2:02 PM

  Another way, using Luis' sample data

  SELECT name, rtrim(ltrim(isnull(parsename(' '+x.name+' ', 2), x.name))) as name2
FROM (VALUES('Luis.Cazares'),('Luis Cazares'),('Luis Cazares.'),('.Luis Cazares')) x(name);

  Again, I strongly recommend that concatenation be avoided for such things.  See my previous response to Luis.

  I missed that. Agreed.

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001