substring extraction (1 Viewer)

[CoffeeGuru]

Hi Guys

Does anyone know how I can retrieve the following information from a field
[CONTENTS]
1 x 17.7ml
2 x 17ml
1 x 181.1ml

I need
17.7
17
181.1

I was using a combination of CAST(LEFT(RIGHT...)) AS Float

but that obviously won't work due to too many variations

Martin

 

[pr2-eugin]

Is it always going to be an x before the measurements? If so something like this?

Val(Mid([CONTENTS], InStr([CONTENTS], " x ") + 2))

 

[CoffeeGuru]

Hi Paul
Sorry my bad, I forgot to mention its T-SQL
VAL is not a valid instruction
So far I've got to
cast(replace(cast(SUBSTRING([CONTENTS],PATINDEX('%'+'x'+'%',[CONTENTS])+2,PATINDEX('%'+'ml'+'%',[CONTENTS])-3)

Martin

 

[CoffeeGuru]

OK I think I cracked it
If anyone has a better way let me know

RTRIM(CAST(cast(round(sum(CAST(REPLACE(CAST(SUBSTRING([CONTENTS],PATINDEX('%'+'x'+'%',[CONTENTS])+2,PATINDEX('%'+'ml'+'%',[CONTENTS])-5)AS numeric),'ml','') AS numeric))/1000,0)as numeric(15,0))AS CHAR))+' Ltrs' AS K

 

[vbaInet]

This post would have been better served in a SQL Server forum.

1. Replace "ml" with ""
2. CharIndex to find position of "x " + 2
3. Substring to get the value