Truncate string in a cell

• john9569

  Grasshopper

  Points: 20

  More actions

  August 11, 2011 at 12:36 pm

  #241953

  Greeting, Experts.

  In my table, there is one column that has the following text:

  http://domain/sub1/sub2/sub3

  https://domain2/sub1/....

  http://domain_n/

  https://anotherdomain/

  .....

  How do I truncate the string to get only down the sub1 level in all cells of that column? so the result should be:

  http://domain/sub1/

  http2://domain2/sub1/

  http://domain_n/

  .....

  Thanks

  John9569

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  August 11, 2011 at 12:56 pm

  #1367515

  Not much detail to go on there but take a look at substring and charindex. Should get you started in the right direction.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Steve

  SSCommitted

  Points: 1844

  More actions

  August 11, 2011 at 12:59 pm

  #1367517

  DECLARE @t TABLE (vals VARCHAR(1024))

  INSERT INTO @t (vals)

  VALUES ('http://domain/sub1/sub2/sub3')

  INSERT INTO @t (vals)

  VALUES ('https://domain2/sub1/....')

  INSERT INTO @t (vals)

  VALUES ('http://domain_n/')

  INSERT INTO @t (vals)

  VALUES ('https://anotherdomain/')

  DECLARE @tImed TABLE

  (

  vals VARCHAR(1024)

  ,interMed INT

  ,interMed2 INT

  )

  INSERT INTO @tImed (vals, interMed)

  SELECT *, PATINDEX('%[a-zA-Z0-9]/[a-zA-Z]%', t.vals)

  FROM @t t

  SELECT REPLACE(ti.vals, SUBSTRING(ti.vals, 1, ti.interMed + 1), '')

  FROM @tImed ti

  UPDATE @tImed

  SET interMed2 = PATINDEX('%/%', REPLACE(ti.vals, SUBSTRING(ti.vals, 1, ti.interMed + 1), ''))

  FROM @tImed ti

  SELECT SUBSTRING(ti.vals, 1, ti.interMed + ti.interMed2)

  FROM @tImed ti

  Steve