convert a varchar to a number in a query

[sjjustina]

Hopefully this is an easy one. I'm trying to get an average of a length field but the field is varchar(8).

This is my query:
SELECT AVG(CONVERT(varchar(8), pcslength, 0)) AS AvgLength
FROM tag_detail
GROUP BY tagnum

The convert doesn't seem to be working. Is there another way to do this?

 

[ptheriault]

I think this might give you what you want.

declare @table table ( id int identity(1,1) 
                     ,pcslength varchar(8))

INSERT INTO @table (pcslength) VALUES('Paul')
INSERT INTO @table (pcslength) VALUES('George')
INSERT INTO @table (pcslength) VALUES('Denis')
INSERT INTO @table (pcslength) VALUES('Alex')

SELECT AVG(LEN(pcslength))
FROM @table

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[sjjustina]

Thanks Dennis but the field contains the lengths as numbers like 10 or 12. I'm not trying to get the length of the data in the field but an average of the numbers in the field.

 

[ptheriault]

Oh, sorry about that.

How about this.

declare @table table ( id int identity(1,1) 
                     ,pcslength varchar(8))

INSERT INTO @table (pcslength) VALUES('12')
INSERT INTO @table (pcslength) VALUES('10')
INSERT INTO @table (pcslength) VALUES('11')
INSERT INTO @table (pcslength) VALUES('10')

SELECT AVG(CONVERT(INT,(pcslength)))
FROM @table

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[sjjustina]

THANKS!

 

[sjjustina]

That works great for the lengths since they are integegers but what the widths are numbers like 4 3/8"? Thanks

 

[ptheriault]

Is that how it looks in the table '4 3/8'?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[sjjustina]

Yes but without any punctuation other than the slash.

 

[ptheriault]

Ok,
If you do this it will return the values that can be converted to int.

declare @table table ( id int identity(1,1) 
                     ,pcslength varchar(8))

INSERT INTO @table (pcslength) VALUES('4 3/8')
INSERT INTO @table (pcslength) VALUES('10')
INSERT INTO @table (pcslength) VALUES('11')
INSERT INTO @table (pcslength) VALUES('10')

SELECT AVG(CONVERT(int,(pcslength)))
FROM @table
WHERE isnumeric(pcslength) > 0

I'm not sure how you can handle the 4 3/8 values. Let me research that.
George, Denis or Alex may already know this.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[AlexCuse]

Wow, you have quite the mess on your hands here. Here is a function to turn your integer + fraction values into numerics (which can then be dealt with as numbers)

[COLOR=blue]create[/color] [COLOR=#FF00FF]function[/color] SplitFraction(@t [COLOR=blue]varchar[/color](666)) 
returns [COLOR=blue]numeric[/color](20,10)
[COLOR=blue]as[/color] 

[COLOR=blue]begin[/color]

[COLOR=blue]declare[/color] @w [COLOR=blue]numeric[/color](20, 10), @n [COLOR=blue]numeric[/color](20, 10), @d [COLOR=blue]numeric[/color](20,10)

[COLOR=blue]select[/color] @w = [COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]left[/color](@t, [COLOR=#FF00FF]charindex[/color]([COLOR=red]' '[/color], @t)-1) [COLOR=blue]as[/color] [COLOR=blue]integer[/color])

[COLOR=blue]select[/color] @n = [COLOR=#FF00FF]cast[/color](
[COLOR=#FF00FF]left[/color](
[COLOR=#FF00FF]substring[/color](@t, [COLOR=#FF00FF]charindex[/color]([COLOR=red]' '[/color], @t) + 1
, len(@t))
, [COLOR=#FF00FF]charindex[/color]([COLOR=red]'/'[/color], [COLOR=#FF00FF]substring[/color](@t, [COLOR=#FF00FF]charindex[/color]([COLOR=red]' '[/color], @t) + 1, len(@t)))-1) 
[COLOR=blue]as[/color] [COLOR=blue]integer[/color])

[COLOR=blue]select[/color] @d = [COLOR=#FF00FF]cast[/color](
[COLOR=#FF00FF]right[/color](@t, 
[COLOR=#FF00FF]charindex[/color]([COLOR=red]'/'[/color], [COLOR=#FF00FF]reverse[/color](@t)) - 1)
[COLOR=blue]as[/color] [COLOR=blue]integer[/color])

[COLOR=blue]return[/color] @w + (@n / @d)

[COLOR=blue]end[/color]

Here is how this function works:

[COLOR=green]--set up test data
[/color][COLOR=blue]insert[/color] @t
[COLOR=blue]select[/color] [COLOR=red]'4 3/8'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'5 16/20'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'666 12/13'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'97 1/29'[/color]

[COLOR=green]--note that it returns fractions in decimal format
[/color][COLOR=blue]select[/color] frac, dbo.SplitFraction(Frac) [COLOR=blue]from[/color] @t

[COLOR=green]--which can be averaged
[/color][COLOR=blue]select[/color] [COLOR=#FF00FF]avg[/color](dbo.SplitFraction(Frac)) [COLOR=blue]from[/color] @t

Hope it helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[ptheriault]

Nice function Alex.
I knew either you, George or Denis would know that.


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

The function fails if there isn't a space.

insert @t
select '4 3/8'
union all select '5 16/20'
union all select '666 12/13'
union all select '97 1/29'
[!]union all select '37'[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

The user will have to take some responsibility for their data, and add the simple bits like that on their own ;-)

Ignorance of certain subjects is a great part of wisdom

 

[sjjustina]

Thank you thank you thank you.

But one more thing, it's saying and invalid parameter has been passed and I think it's because the field can be blank. Can you return nothing if its blank?

Thanks again, Sarah

 

[ptheriault]

Use ISNULL to take care of that.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[AlexCuse]

I'd think that in that situation, you want to return 0 for correct summing/averaging, rather than NULL. Try this out:

[COLOR=blue]alter[/color] [COLOR=#FF00FF]function[/color] SplitFraction(@t [COLOR=blue]varchar[/color](100)) 
returns [COLOR=blue]numeric[/color](20,10)
[COLOR=blue]as[/color] 

[COLOR=blue]begin[/color]

[COLOR=blue]declare[/color] @w [COLOR=blue]numeric[/color](20, 10), @n [COLOR=blue]numeric[/color](20, 10), @d [COLOR=blue]numeric[/color](20,10)

[COLOR=blue]if[/color] len(@t) = 0
[COLOR=blue]begin[/color]
	[COLOR=blue]select[/color] @w = 0
	[COLOR=blue]select[/color] @n = 0
	[COLOR=blue]select[/color] @d = 1
[COLOR=blue]end[/color]

[COLOR=blue]else[/color]
[COLOR=blue]begin[/color]
	[COLOR=blue]select[/color] @w = [COLOR=blue]case[/color] [COLOR=blue]when[/color] [COLOR=#FF00FF]charindex[/color]([COLOR=red]' '[/color], @t) = 0 [COLOR=blue]then[/color]
		[COLOR=#FF00FF]cast[/color](@t [COLOR=blue]as[/color] [COLOR=blue]numeric[/color](20,10)) [COLOR=blue]else[/color]
		[COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]left[/color](@t, [COLOR=#FF00FF]charindex[/color]([COLOR=red]' '[/color], @t)-1) [COLOR=blue]as[/color] [COLOR=blue]numeric[/color](20,10)) [COLOR=blue]end[/color]
	
	[COLOR=blue]select[/color] @n = [COLOR=blue]case[/color] [COLOR=blue]when[/color] [COLOR=#FF00FF]charindex[/color]([COLOR=red]' '[/color], @t) = 0 [COLOR=blue]then[/color]
		0 [COLOR=blue]else[/color]
		[COLOR=#FF00FF]cast[/color](
			[COLOR=#FF00FF]left[/color](
			[COLOR=#FF00FF]substring[/color](@t, [COLOR=#FF00FF]charindex[/color]([COLOR=red]' '[/color], @t) + 1
			, len(@t))
			, [COLOR=#FF00FF]charindex[/color]([COLOR=red]'/'[/color], [COLOR=#FF00FF]substring[/color](@t, [COLOR=#FF00FF]charindex[/color]([COLOR=red]' '[/color], @t) + 1, len(@t)))-1) 
			[COLOR=blue]as[/color] [COLOR=blue]numeric[/color](20,10))
		[COLOR=blue]end[/color]

	[COLOR=blue]select[/color] @d = [COLOR=blue]case[/color] [COLOR=blue]when[/color] [COLOR=#FF00FF]charindex[/color]([COLOR=red]' '[/color], @t) = 0 [COLOR=blue]then[/color]
		1 [COLOR=blue]else[/color]
		[COLOR=#FF00FF]cast[/color](
			[COLOR=#FF00FF]right[/color](@t, 
			[COLOR=#FF00FF]charindex[/color]([COLOR=red]'/'[/color], [COLOR=#FF00FF]reverse[/color](@t)) - 1)
			[COLOR=blue]as[/color] [COLOR=blue]numeric[/color](20,10))
		[COLOR=blue]end[/color]
[COLOR=blue]end[/color]

[COLOR=blue]return[/color] @w + (@n / @d)


[COLOR=blue]end[/color]

And another test:

[COLOR=green]--set up test data
[/color][COLOR=blue]declare[/color] @t [COLOR=blue]table[/color] (frac [COLOR=blue]varchar[/color](666))
[COLOR=blue]insert[/color] @t
[COLOR=blue]select[/color] [COLOR=red]'4 3/8'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'5 16/20'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'666 12/13'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'97 1/29'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'37'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]''[/color]
[COLOR=green]--note that it returns fractions in decimal format
[/color][COLOR=blue]select[/color] frac, dbo.SplitFraction(Frac) [COLOR=blue]from[/color] @t

[COLOR=green]--which can be averaged
[/color][COLOR=blue]select[/color] [COLOR=#FF00FF]avg[/color](dbo.SplitFraction(Frac)) [COLOR=blue]from[/color] @t

Hopefully there are not any letters in there ;-)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[sjjustina]

Sorry for not posting some data, I'm new to this! I'm sorry I wasted your time but in looking through the data I forgot about the milimeters.
I do appreciate all the quick answers!

08
10
10
11
11
12
12
8 3/8
8 3/8
04
04
04
04
04
04
04
04
04
04
142M
142M
142M
142M
142M
142M
142M
142M
142M
142M
142M
142M
142M
142M
142M
142M
05
05
05
05
03
03
03
03
03
03

 

[gmmastros]

[COLOR=blue]Alter[/color] [COLOR=#FF00FF]Function[/color] CalculateExpression
	(@MathExpression [COLOR=blue]VarChar[/color](100))
Returns [COLOR=blue]Numeric[/color](20,10)
[COLOR=blue]As[/color]
[COLOR=blue]Begin[/color]
[COLOR=blue]Set[/color] @MathExpression = [COLOR=#FF00FF]Replace[/color](@MathExpression, [COLOR=red]' '[/color], [COLOR=red]'+'[/color])
[COLOR=blue]DECLARE[/color] @object [COLOR=blue]int[/color]
[COLOR=blue]DECLARE[/color] @hr [COLOR=blue]int[/color]
[COLOR=blue]DECLARE[/color] @src [COLOR=blue]varchar[/color](255), @desc [COLOR=blue]varchar[/color](255)
[COLOR=blue]Declare[/color] @Out [COLOR=blue]VarChar[/color](100)
[COLOR=blue]EXEC[/color] @hr = sp_OACreate [COLOR=red]'MSScriptControl.ScriptControl'[/color], @object [COLOR=#FF00FF]OUT[/color]
[COLOR=blue]exec[/color] sp_OASetProperty @Object, [COLOR=red]'Language'[/color], [COLOR=red]'vbscript'[/color]
[COLOR=blue]exec[/color] sp_OAMethod @Object, [COLOR=red]'Eval'[/color], @Out [COLOR=blue]Output[/color], @MathExpression
[COLOR=blue]Return[/color] @Out
[COLOR=blue]End[/color]

This isn't tested, but you can test it yourself like this...

[COLOR=blue]Select[/color] dbo.CalculateExpression([COLOR=red]''[/color])
[COLOR=blue]Select[/color] dbo.CalculateExpression([COLOR=red]'10 3/8'[/color])
[COLOR=blue]Select[/color] dbo.CalculateExpression([COLOR=red]'15'[/color])
[COLOR=blue]Select[/color] dbo.CalculateExpression([COLOR=red]'(1+5/10)^2'[/color])

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

You need to average metric and english units as well? Where the heck did this database come from?

Maybe this will help, it will convert MM to inches (in a decimal just like the other function)

[COLOR=blue]create[/color] [COLOR=#FF00FF]function[/color] MMtoInDec (@t [COLOR=blue]varchar[/color](666)) 
returns [COLOR=blue]numeric[/color](20, 10)
[COLOR=blue]as[/color]
[COLOR=blue]begin[/color]

[COLOR=blue]select[/color] @t = [COLOR=#FF00FF]replace[/color](@t, [COLOR=red]'M'[/color], [COLOR=red]''[/color])
[COLOR=blue]return[/color] ([COLOR=#FF00FF]cast[/color](@t [COLOR=blue]as[/color] [COLOR=blue]numeric[/color](20,10)) / 10) * 0.3937008

[COLOR=blue]end[/color]

and of course, another test:

[COLOR=green]--set up test data
[/color][COLOR=blue]declare[/color] @t [COLOR=blue]table[/color] (frac [COLOR=blue]varchar[/color](666))
[COLOR=blue]insert[/color] @t
[COLOR=blue]select[/color] [COLOR=red]'4 3/8'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'5 16/20'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'666 12/13'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'97 1/29'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'37'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]''[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'142M'[/color]

[COLOR=green]--note that it returns fractions in decimal format
[/color][COLOR=blue]select[/color] frac, [COLOR=blue]case[/color] [COLOR=blue]when[/color] [COLOR=#FF00FF]charindex[/color]([COLOR=red]'M'[/color], frac) > 0 [COLOR=blue]then[/color]
dbo.MMtoInDec(frac) [COLOR=blue]else[/color]
dbo.SplitFraction(Frac) [COLOR=blue]end[/color] [COLOR=blue]from[/color] @t

[COLOR=green]--which can be averaged
[/color][COLOR=blue]select[/color] [COLOR=#FF00FF]avg[/color](
[COLOR=blue]case[/color] [COLOR=blue]when[/color] [COLOR=#FF00FF]charindex[/color]([COLOR=red]'M'[/color], frac) > 0 [COLOR=blue]then[/color]
dbo.MMtoInDec(frac) [COLOR=blue]else[/color]
dbo.SplitFraction(Frac) [COLOR=blue]end[/color]) [COLOR=blue]from[/color] @t

Hope this helps (note- I do not know how accurate the CM to inch conversion is, that is the first value I found off of Google)

Alex

Ignorance of certain subjects is a great part of wisdom

 

[sjjustina]

You guys are soooo wonderful. It works!!!

Hurricane Katrina made me change jobs and I went from Healthcare to a Lumber Import/Exporter. They do hardwoods, flooring, decking, plywood in just about every unit of measure.

We're upgrading to the latest version of the inventory software and there are virtually NO reports and I have to write them instead. Just about ever one of them needs the average width and length in each bundle.

I thought Healthcare was complicated!

You guys have saved me a huge amount of work. If you're ever in New Orleans I'll buy you dinner.