Using SQL to extract certain values

[bcurrey]

I’ve been able to partially figure out the solution here, but can’t quite get all of it. I have merged 15 spreadsheets into 1 Access DB using SQL. I have one column of data that has a varying number of characters. I need to extract the numbers from it and place in separate columns.

Here’s the query I’m currently using:

Select


[bsc].[Warranty SKU]
, Mid([bsc].[Warranty SKU], Instr(1,[bsc].[Warranty SKU], "$") + 1) AS MinPrice
, Mid([bsc].[Warranty SKU], InStr(1,[bsc].[Warranty SKU], "-") + 1) as MaxPrice
From bsc


Here’s the data my query returns.

Warranty Sku	MinPrice	MaxPrice
MickeyD Hamburgers SVC 36 MOS $0-99 P	0-99 P	99 P
MickeyD Hamburgers SVC 36 MOS $10000-14999 P	10000-14999 P	14999 P
MickeyD Hamburgers SVC 36 MOS $1000-1999 P	1000-1999 P	1999 P
MickeyD Hamburgers SVC 36 MOS $100-199 P	100-199 P	199 P
MickeyD Hamburgers SVC 36 MOS $15000-29999 P	15000-29999 P	29999 P
SMBBB SJ VU LIFETM JWLRY $150-199 P	1500-1999 P	1999 P
SAMS Burger SVC 36M $500-4999 P	500-4999 P	4999 P
NEX Hamburgers SVC 24 MOS $0-99 P	0-99 P	99 P
AAFES Hamburgers SVC 24 MOS $1000-2999 P	1000-2999 P	2999 P
MickeyD LIFETIME HAMB SV $2000-2999 P	2000-2999 P	2999 P
MickeyD LIFETIME HAMB SV $200-499 P	200-499 P	499 P

Here’s the data format that I want to get. Any help would be much appreciated!!!

Warranty Sku	MinPrice	MaxPrice
MickeyD Hamburgers SVC 36 MOS $0-99 P	0	99
MickeyD Hamburgers SVC 36 MOS $10000-14999 P	10000	14999
MickeyD Hamburgers SVC 36 MOS $1000-1999 P	1000	1999
MickeyD Hamburgers SVC 36 MOS $100-199 P	100	199
MickeyD Hamburgers SVC 36 MOS $15000-29999 P	15000	29999
SMBBB SJ VU LIFETM JWLRY $150-199 P	1500	1999
SAMS Burger SVC 36M $500-4999 P	500	4999
NEX Hamburgers SVC 24 MOS $0-99 P	0	99
AAFES Hamburgers SVC 24 MOS $1000-2999 P	1000	2999
MickeyD LIFETIME HAMB SV $2000-2999 P	2000	2999
MickeyD LIFETIME HAMB SV $200-499 P	200	499
	750	999

[pbaldy]

The Mid() function has another argument you aren't providing, the number of characters. You can use InStr() on both the $ and - and subtract to get the number of characters. The Val() function should help with the second.

[bcurrey]

Thank you Paul. That helped! I made a bit more progress after your help, but I have no idea how to go any further. Can you tell me how I would apply the VAL function to what I already have? Really appreciate it.


, Mid([bsc].[Warranty SKU], Instr(1,[bsc].[Warranty SKU], "$") + 1, Len([bsc].[Warranty SKU]) - Instr([bsc].[Warranty SKU], '-')-2)AS MinPrice
, Mid([bsc].[Warranty SKU], InStr(1,[bsc].[Warranty SKU], "-") + 1) AS MaxPrice

Warranty Sku	MinPrice	MaxPrice
MickeyD Hamburgers SVC 36 MOS $0-99 P	0-	99 P
MickeyD Hamburgers SVC 36 MOS $10000-14999 P	10000	14999 P
MickeyD Hamburgers SVC 36 MOS $1000-1999 P	1000	1999 P
MickeyD Hamburgers SVC 36 MOS $100-199 P	100	199 P
MickeyD Hamburgers SVC 36 MOS $15000-29999 P	15000	29999 P

[pbaldy]

I was thinking

Val(Mid([bsc].[Warranty SKU], InStr(1,[bsc].[Warranty SKU], "-") + 1)) AS MaxPrice

[Bulzie]

Maybe something lilke below.

Mid([bsc].[Warranty SKU], Instr(1,[bsc].[Warranty SKU], "$") + 1, (Instr(1,[bsc].[Warranty SKU], "-")) - (Instr([bsc].[Warranty SKU], '$') +1)) AS MinPrice

and pbaldy line for the MaxPrice or:

Mid([bsc].[Warranty SKU], Instr(1,[bsc].[Warranty SKU], "-") + 1, (Instr(1,[bsc].[Warranty SKU], " ")) - (Instr([bsc].[Warranty SKU], '-') +1)) AS MaxPrice

[bcurrey]

Sweet! I've never used that before! Another new item to put in the tool belt. Really appreciate it!

[bcurrey]

Hello again. The SQL worked create and did exactly what I needed. However, I'm not able to filter on the fields that were created. I get an error message that says "Enter a valid value" when I enter a value of > 200. If I enter greater than "200", I get the same message. Any ideas on what I'm doing wrong here? Thanks!


Here's my code:

SELECT bsc.*
, (Val(Mid([bsc].[Warranty SKU],InStr(1,[bsc].[Warranty SKU],"$")+1,Len([bsc].[Warranty SKU])-InStr([bsc].[Warranty SKU],'-')-2))) AS MinPrice
, (Val(Mid([bsc].[Warranty SKU],InStr(1,[bsc].[Warranty SKU],"-")+1))) AS MaxPrice

FROM (SELECT* FROM Rogers

UNION

Select * from AAFES

) AS bsc;