Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Extract first numeric value from a string Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, June 27, 2013 12:57 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, December 02, 2013 9:20 AM Points: 139, Visits: 265
 Brothers,I need to parse the first numeric value from a string that usually contains several numerics.This function works OK when the first number has no decimals ( 1 - returns '500000'). In #2 (naturally!) the result is 0Can you spare some help? `alter FUNCTION dbo.fnExtractDigits (@inString VARCHAR(8000))RETURNS VARCHAR(8000) ASBEGIN DECLARE @X VARCHAR(100)Select @X=@inString Select @X= SubString(@X,PATINDEX('%[0-9]%',@X),Len(@X))Select @X= SubString(@X,0,PATINDEX('%[^0-9]%',@X))RETURN @XEND-- 1. select dbo.fnExtractDigits ('NYSTATIN SUSP 500000 UNIT = 5 ML (1 5 ML CUP)')-- 2. select dbo.fnExtractDigits ('NYSTATIN SUSP 0.75 UNIT = 5 ML (1 5 ML CUP)')`
Post #1468299
 Posted Thursday, June 27, 2013 1:10 PM
 SSChampion Group: General Forum Members Last Login: Today @ 3:29 PM Points: 10,886, Visits: 10,053
 I would recommend NOT doing this as a scalar function. Scalar functions are not good for performance. You can easily leverage the DelimitedSplit8K function for this.`select top 1 *from dbo.DelimitedSplit8K('NYSTATIN SUSP 500000 UNIT = 5 ML (1 5 ML CUP)', ' ')where Item not like '%[^0-9.]%'select top 1 *from dbo.DelimitedSplit8K('NYSTATIN SUSP 0.75 UNIT = 5 ML (1 5 ML CUP)', ' ')where Item not like '%[^0-9.]%'`You can find the code the that function by following the link in my signature about splitting strings. _______________________________________________________________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 #1468307
 Posted Thursday, June 27, 2013 1:11 PM
 SSC Eights! Group: General Forum Members Last Login: Today @ 2:57 PM Points: 872, Visits: 6,425
 Sis,Provided you don't have '.' anywhere except as a decimal separator:`alter FUNCTION dbo.fnExtractDigits (@inString VARCHAR(8000))RETURNS VARCHAR(8000) ASBEGIN DECLARE @X VARCHAR(100)Select @X=@inString Select @X= SubString(@X,PATINDEX('%[0-9]%',@X),Len(@X))Select @X= SubString(@X,0,PATINDEX('%[^.0-9]%',@X))RETURN @XEND` And then again, I might be wrong ...David Webb
Post #1468308
 Posted Thursday, June 27, 2013 1:32 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, December 02, 2013 9:20 AM Points: 139, Visits: 265
 Thanks Sean. Will read (love charts!)
Post #1468316
 Posted Thursday, June 27, 2013 1:34 PM
 SSChampion Group: General Forum Members Last Login: Today @ 3:29 PM Points: 10,886, Visits: 10,053
 Let's take the excellent example from David and turn it into a iTVF instead of a scalar function.`create FUNCTION dbo.fnExtractDigits (@inString VARCHAR(8000))RETURNS tablereturn select SubString( SubString(@inString, PATINDEX('%[0-9]%', @inString), Len(@inString)), 0, PATINDEX('%[^.0-9]%', SubString(@inString,PATINDEX('%[0-9]%',@inString),Len(@inString)))) as MyValue`Now we can really leverage some power and performance.`--need to setup our test data;with myCte(SomeKey, SomeValue) as( select 1, 'NYSTATIN SUSP 500000 UNIT = 5 ML (1 5 ML CUP)' union all select 2, 'NYSTATIN SUSP 0.75 UNIT = 5 ML (1 5 ML CUP)')--now we can retrieve itselect *from myCtecross apply dbo.fnExtractDigits(SomeValue) s` _______________________________________________________________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 #1468318
 Posted Thursday, June 27, 2013 1:37 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, December 02, 2013 9:20 AM Points: 139, Visits: 265
 Dave - thanks, it works -need to play to make sure it's ok in other stringsThanks(I can only be your brother, not sister - need to ask dad
Post #1468321
 Posted Thursday, June 27, 2013 1:44 PM
 SSC Eights! Group: General Forum Members Last Login: Today @ 2:57 PM Points: 872, Visits: 6,425
 Sean has the best idea. His implementation will be far better behaved than the code I posted. And then again, I might be wrong ...David Webb
Post #1468325
 Posted Thursday, June 27, 2013 1:47 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, December 02, 2013 9:20 AM Points: 139, Visits: 265
 Ha! Sean, you elegantly rewrote my function (stollen from somwhere anyway).I do not think that to use cte in my case is a good idea - I use this function within a select statement from a big table - not sure that cte can be used here`SELECT Real_Date_Time ChartedAt,DRUG_INFO_1,LEFT(drug_info_1,CHARINDEX(CHAR(32),drug_info_1)) Drug ,dbo.fnExtractDigits(DRUG_INFO_1) as DoseOrFirstNumeric , Coalesce(case charindex('MG', DRUG_INFO_1) when 0 then null else 'MG' end --Mg ,case charindex('GM', DRUG_INFO_1) when 0 then null else 'GM' end --GM ,case charindex('UNIT', DRUG_INFO_1) when 0 then null else 'UNITS' end ----UNITS ) ,ROUTE ,NO_OF_INGREDIENTS,PMP,pat_num ,ADMIN_STATUS FROM MAK_Data.dbo.CHRT_Tran Where LEFT(drug_info_1,CHARINDEX(CHAR(32),drug_info_1)) IN ( 'abacavir', 'acyclovir', 'amikacin', 'amoxicillin', 'amoxicillin-clavulanic', 'amphotericin', 'ampicillin', 'ampicillin-sulbactam', 'atazanavir', 'atovaquone', 'azithromycin', 'aztreonam', 'cefazolin', 'cefdinir', 'cefepime', 'cefixime', 'cefotaxime', 'cefotetan', 'cefoxitin', 'cefpodoxime proxetil', 'ceftazidime', 'ceftriaxone', 'cefuroxime', 'cefuroxime axetil', 'cephalexin', 'ciprofloxacin', 'clarithromycin', 'clindamycin', 'colistimethate', 'dapsone', 'daptomycin', 'demeclocycline', 'dicloxacillin', 'doxycycline', 'efavirenz', 'ertapenem', 'erythromycin', 'erythromycin', 'ethambutol', 'fluconazole', 'gentamicin', 'hydroxychloroquine', 'imipenem', 'isoniazid', 'itraconazole', 'ketoconazole', 'lamivudine', 'levofloxacin', 'linezolid', 'meropenem', 'methenamine', 'metronidazole', 'minocycline', 'nafcillin', 'neomycin', 'nevirapine', 'nitrofurantoin', 'nystatin', 'oseltamivir', ' penicillin', 'pentamidine', 'piperacillin-tazobactam (single)', 'pyrazinamide', 'ribavirin', 'rifabutin', 'rifampin', 'ritonavir', 'sulfamethoxazole/trimethoprim', 'sulfasalazine', 'tenofovir', 'terbinafine', 'tetracycline', 'tigecycline', 'tobramycin', 'trimethoprim', 'valacyclovir', 'vancomycin', 'voriconazole' )and ROUTE IN ('iv','PO') AND Real_Date_Time >'6/1/2013' and ADMIN_STATUS ='admin'`
Post #1468329
 Posted Thursday, June 27, 2013 2:01 PM
 SSChampion Group: General Forum Members Last Login: Today @ 3:29 PM Points: 10,886, Visits: 10,053
 valeryk2000 (6/27/2013)Ha! Sean, you elegantly rewrote my function (stollen from somwhere anyway).I do not think that to use cte in my case is a good idea - I use this function within a select statement from a big table - not sure that cte can be used hereThe only reason there is a cte there is for testing. I don't have a table with the values you posted so I just stuck them in a cte. It is merely an example of usage. That could a table, a cte, whatever.They way you wrote the function and are using it is a scalar function. They are horrible for performance. Look at how I used the iTVF with cross apply instead of calling the function as a column. The performance benefits are HUGE!!!! _______________________________________________________________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 #1468338
 Posted Friday, June 28, 2013 9:18 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, December 02, 2013 9:20 AM Points: 139, Visits: 265
 Ok. I'll try it (never tried before)
Post #1468588

 Permissions