how to trim away the first 'x' and last 'y number of characters from a string

• siri_rmsi

  Old Hand

  Points: 348

  More actions

  May 4, 2016 at 7:18 am

  #400111

  Hi all,

  how to trim away the first 'x' and last 'y number of characters from a string

  for example:

  adventure-works\mary0

  adventure-works\jill0

  adventure-works\james1

  adventure-works\peter0

  adventure-works\jo0

  adventure-works\guy1

  adventure-works\mark1

  adventure-works\britta0

  adventure-works\margie0

  adventure-works\rebecca0

  i just need

  mary

  jill

  james

  peter

  joo

  ...... in my column

  using substring: select substring(LoginID,17,_____) from abc(Here LoginID is the column name)

  how to give the third argument??

  Thanks,

  siri

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 4, 2016 at 7:24 am

  #1876576

  Replace 'y' with the number of characters to trim off the end

  DECLARE @STR VARCHAR(100) = 'adventure-works\peter0'

  select substring(@str,17,LEN(@str)-(17-1) - y)

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Y.B.

  SSChampion

  Points: 11649

  More actions

  May 4, 2016 at 9:25 am

  #1876642

  Do you care about running that against any other possible db's? You might want something a little more generic.

  DECLARE @strings TABLE (LoginID VARCHAR(100))

  INSERT INTO @strings

  VALUES ('adventure-works\peter0'), ('Northwind\mary0'), ('Test\mark1')

  SELECT

  SUBSTRING(LoginID,CHARINDEX('\', LoginID) + 1, LEN(LoginID)-CHARINDEX('\', LoginID)-1)

  FROM

  @strings

  This still implies that you only have 1 unwanted character at the end. If you want something that also strips out any amount of numbers than it gets a little trickier but still possible. The less you hard code values the more re-usable your code will be.

  Cheers,

  ---

  SELECT quote FROM brain WHERE original = 1
  0 rows returned

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  May 4, 2016 at 1:03 pm

  #1876709

  Another way. And this will work for multiple trailing numbers or when there aren't any.

  DECLARE @strings TABLE (LoginID VARCHAR(100))

  INSERT INTO @strings

  VALUES ('adventure-works\peter0'), ('Northwind\mary009'), ('Test\mark');

  SELECT SUBSTRING(p, 1, ISNULL(NULLIF(PATINDEX('%[0-9]%',p),0),8000)-1)

  FROM

  (

  SELECT p = SUBSTRING(LoginID,CHARINDEX('\', LoginID)+1,8000) FROM @strings

  ) prep;

  "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