pulling multiple numbers out of a string
-
I need help pulling multiple number values out of a string
field name is Details and here are some examples of the values
DETAILS
Maximum width is 13'2", maximum length is 30'. Minimum total size for all custom rugs is 25 sq/ft.
Maximum width is 15', maximum length is 25'. Minimum total size for all custom rugs is 25 sq/ft.
Maximum width is 12', maximum length is 30'. Minimum total size for all custom rugs is 15 sq/ft.
i need so get the number values pulled out and into their own fields, and the numbers aren't always the same
here are two hopeful outputs
thanks in advance!
-
Is it really SQL 7, 2000 you are using?
-
If you are using SQL 7/2000 and the PARSENAME function is available (I'm not sure if it is), you can try the following. This approach is making a BIG assumption about the descriptions stored in your "Details" column as well as the need to get the data in a single SELECT statement. Hope this helps steer you into a solution that works for you.
/*
Maximum width is 13'2", maximum length is 30'. Minimum total size for all custom rugs is 25 sq/ft.
Maximum width is 15', maximum length is 25'. Minimum total size for all custom rugs is 25 sq/ft.
Maximum width is 12', maximum length is 30'. Minimum total size for all custom rugs is 15 sq/ft.
*/
-- create a dummy table
if( object_id( 'tempdb..#orders') is not null) drop table #orders;
create table #orders(
order_id int identity(1,1) not null primary key clustered,
details nvarchar(255) not null
);
-- load sample data
insert #orders( details)
values( 'Maximum width is 13''2", maximum length is 30''. Minimum total size for all custom rugs is 25 sq/ft.'),
( 'Maximum width is 15'', maximum length is 25''. Minimum total size for all custom rugs is 25 sq/ft.'),
( 'Maximum width is 12'', maximum length is 30''. Minimum total size for all custom rugs is 15 sq/ft.')
-- extract the "width", "length" and "size" using SQL 7, 2000
-- *IF* detail descriptions are consistent *AND* PARSENAME is available in your version of SQL
select details,
x.measurements,
replace( parsename( x.measurements, 3), 'in', '"') as maxW,
replace( parsename( x.measurements, 2), 'in', '"') as maxL,
parsename( x.measurements, 1) as minSqF
from #orders o
cross apply (
select replace( replace( replace( replace( replace(
details, 'Maximum width is ', '')
, ', maximum length is ', '.')
, '. Minimum total size for all custom rugs is ', '.')
, ' sq/ft.', '')
, '"', 'in') as measurements
) x -
Here are the results:
-
Neither CROSS APPLY nor the multiline version of VALUES is available in 7/2000.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jonathan AC Roberts wrote:
Is it really SQL 7, 2000 you are using?
@tpd1989 ,
This is the KEY question on this thread. Please confirm that you are actually using SQL 7 or 2000 or not. It DOES make a HUGE difference for this problem.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Noted, thanks Jeff!
-
Here's the test data using the "old fashioned" way that works in all versions of SQL Server since at least 6.5.
--===== Create the test table from what the OP provided.
IF OBJECT_ID('tempdb..#TempTable','U') IS NOT NULL DROP TABLE #TempTable
;
SELECT d.Details
INTO #TestTable
FROM (
SELECT Details = 'Maximum width is 13''2", maximum length is 30''. Minimum total size for all custom rugs is 25 sq/ft.' UNION ALL
SELECT 'Maximum width is 15'', maximum length is 25''. Minimum total size for all custom rugs is 25 sq/ft.' UNION ALL
SELECT 'Maximum width is 12'', maximum length is 30''. Minimum total size for all custom rugs is 15 sq/ft.'
) d
;The OP provided two possible outputs but the "data" for only the first one. With that in mind, I'm only solving for the first one using the given data above.
Since all of the rows contain identical data except for the actual numeric values, I'm going to assume that will always be the case until the OP provides more substantial data. Since it's always the same, we can simply REPLACE what we don't want with an empty string and a key comma with a period. Then, PARSENAME does make quick work of this problem. If we actually need to, we can fire up a TALLY table (which does work on 7/2000) to make quick work of this type of thing but I'm going with the faster direct approach instead of a generic approach that can solve world hunger.
Here's the code.
SELECT minW = REPLACE(PARSENAME(d.CleanString,4),'!','"')
,minL = REPLACE(PARSENAME(d.CleanString,3),'!','"')
,minSqF = REPLACE(PARSENAME(d.CleanString,2),'!','"')
FROM (
SELECT CleanString = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
Details
,'Maximum width is ','')
,' maximum length is ','')
,' Minimum total size for all custom rugs is ','')
,',','.')
,'"','!') --Need to do this because PARSENAME doesn't like double-quotes.
+'X' --Simple method to "ignore" the last period.
FROM #TestTable
) d
;--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff, The second code option works exactly as I need it. I Saw Bill's first which also works so I marked that as the answer but wanted to let you know this also worked.
I'll be saving both for future uses.
Also sorry for not replying earlier, the email alerts were being sent to junk mail.
Thank you both so much!
- tpd1989 wrote:
Hi Jeff, The second code option works exactly as I need it. I Saw Bill's first which also works so I marked that as the answer but wanted to let you know this also worked.
I'll be saving both for future uses.
Also sorry for not replying earlier, the email alerts were being sent to junk mail.
Thank you both so much!
Thanks for the feedback but... If Bill's code worked, does that mean you're actually using at least SQL Server 2005?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
So I wasn't entirely sure when I made this post but looking now is seems I have Microsoft SQL Server 2017. Sorry about that, I was searching for help and or any insight possible on my issue and it brought be here.
-
Ok... that explains why Bill's good code worked. Thanks for coming back with that.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply