Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Substring and trimming Expand / Collapse
Author
Message
Posted Tuesday, January 07, 2014 2:20 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 10:58 AM
Points: 1,101, Visits: 794
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.
Post #1528671
Posted Tuesday, January 07, 2014 2:30 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 3:43 PM
Points: 544, Visits: 806
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
Post #1528676
Posted Tuesday, January 07, 2014 2:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 12,034, Visits: 11,062
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1528685
Posted Tuesday, January 07, 2014 3:05 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 10:58 AM
Points: 1,101, Visits: 794
Okay Thanks a lot
Post #1528693
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse