Substring and trimming

• dallas13

  SSCertifiable

  Points: 7491

  More actions

  January 7, 2014 at 2:20 pm

  #146451

  Here is what I have,

  DECLARE @document varchar(200);

  DECLARE @document1 varchar(200);

  SELECT @document = '68.33 GB Total, 56.75 GB (83%) Free, 3% Fragmented (7% file fragmentation)'

  SELECT @document1 = '137 GB Total, 83.83 GB (61%) Free, 39% Fragmented (78% file fragmentation)'

  I need to get how much fragmentation. So in first one its 7 and in other its 78

  SELECT substring(@document,charindex('Fragmented',@document,1)+12,2) AS file_fragpct --gives 7%

  SELECT substring(@document1,charindex('Fragmented',@document1,1)+12,2) AS file_fragpct--gives 78

  but it gives me values= 7% and 78. I only need values. I dont need '%' sign.

  I want only one standard query that satisfies my condition.

• Keith Tate

  SSCrazy Eights

  Points: 9938

  More actions

  January 7, 2014 at 2:30 pm

  #1678238

  Please edit your post to include consumable DDL and sample data scripts. This will make answering you question a lot easier.

  ---

  
  Microsoft Certified Master - SQL Server 2008
  Follow me on twitter: @keith_tate

  Forum Etiquette: How to post data/code on a forum to get the best help[/url]

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  January 7, 2014 at 2:43 pm

  #1678245

  You can just use replace around what you already have.

  replace(substring(f.frag_pct, charindex('Fragmented', f.frag_pct, 1) + 12, 2), '%', '') AS file_fragpct

  _______________________________________________________________

  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/

• dallas13

  SSCertifiable

  Points: 7491

  More actions

  January 7, 2014 at 3:05 pm

  #1678251

  Okay Thanks a lot