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 7, 2014 2:20 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 11, 2014 10:18 AM
Points: 1,122, Visits: 895
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 7, 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: Today @ 3:29 PM
Points: 597, Visits: 936
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 7, 2014 2:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:05 PM
Points: 13,320, Visits: 12,804
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 7, 2014 3:05 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 11, 2014 10:18 AM
Points: 1,122, Visits: 895
Okay Thanks a lot
Post #1528693
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse