quote:

---

Originally posted by JimL

I forgot the best part They want one Field not 3.

---

DECLARE @x varchar(15), @y int, @z int

SELECT @x = '2 1/4'

SELECT 	  LEFT (@x,CHARINDEX(' ',@x)) AS WholeNumber
	, SUBSTRING(@x,CHARINDEX(' ',@x)+1,CHARINDEX('/',@x)-CHARINDEX(' ',@x)-1) AS Numerator
	, SUBSTRING(@x,CHARINDEX('/',@x)+1,LEN(@x)-CHARINDEX('/',@x)) As Denominator

quote:

---

Originally posted by JimL

I think your getting at pulling it a part with a substing?

Here is some examples of data.

25 13/16
25 13 / 16
25 13/ 16
25 13 /16
25-13/16
25, 13/16
25 and 13/16
25 & 13/16
25 13/16
25_13/16
25 + 13/16
25.8125

And no I am not Kidding unfortunatly.

unless I can present enough evidence to convince them.

Jim
Users <> Logic

---

quote:

---

You still got your Ranger stuff?



Brett

8-)

---

quote:

---

Originally posted by AjarnMark

Jim, I'd give some serious consideration to storing the data as a normal numeric and write a couple of functions to convert decimal to fraction for display and fraction to decimal for input. That way all code behind the scenes can stay normal for doing math, etc.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

---

USE Northwind
GO

CREATE TABLE myTable99(x varchar(15))
GO

INSERT INTO myTable99(x)
SELECT '25 13/16' UNION ALL
SELECT '25 13 / 16' UNION ALL
SELECT '25 13/ 16' UNION ALL
SELECT '25 13 /16' UNION ALL
SELECT '25-13/16' UNION ALL
SELECT '25, 13/16' UNION ALL
SELECT '25 and 13/16' UNION ALL
SELECT '25 & 13/16' UNION ALL
SELECT '25 13/16' UNION ALL
SELECT '25_13/16' UNION ALL
SELECT '25 + 13/16' UNION ALL
SELECT '25.8125'
GO

UPDATE myTable99 Set x = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
		     x,'-',' '),'and',' '),'_',' '),'+',' '),'&',' '),',',' ')
GO

SELECT    SUBSTRING(x,1,CHARINDEX(' ',x)-1)
	, REPLACE(SUBSTRING(x,CHARINDEX(' ',x)+1,LEN(x)-CHARINDEX(' ',x)+1),' ','')
FROM myTable99
WHERE CHARINDEX(' ',x) > 1
GO
 
ALTER TABLE myTable99 ADD y float
GO

UPDATE t SET y = 
	  CONVERT(integer,SUBSTRING(x,1,CHARINDEX(' ',x)-1))+
	 (CONVERT(float,SUBSTRING(x,CHARINDEX(' ',x)+1,CHARINDEX('/',x)-CHARINDEX(' ',x)-1))/
	  CONVERT(float,SUBSTRING(x,CHARINDEX('/',x)+1,LEN(x)-CHARINDEX('/',x))))
FROM myTable99 t
WHERE CHARINDEX(' ',x) > 1
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

quote:

---

Originally posted by JimL

Thanks For the help.

And for Listening to me rant and rave.



Jim
Users <> Logic

---

quote:

---

In short it’s a mess and not worth the time it takes to build. Remember we are talking about the possibility of doing this to every variable and result in a set of formulas.......I understand what jeff is saying, the computations are to complex.

---

quote:

---

Originally posted by JimL

Bret actually has the right idea. (As usual) (don’t get a big head LOL)

---

--declare some vars
declare @decimal numeric(38,9),
		@z numeric(38,9),
		@accuracy  numeric(38,9),
		@prev2Xdenom int,
		@numerator int,
		@denominator int,
		@temporary int,
		@end int,
		@fraction varchar(22)

--input vars
set @decimal = .333333
set @accuracy = .000000001  --deterines the accuracy level


--Initialize local vars
set @end = 0
set @z = @decimal
set @prev2xdenom = 0
set @denominator = 1

fraction_loop:

	set  @z = 1/(@z - convert(int,@z))
	set @temporary = @denominator
	set @denominator = @denominator * convert(int,@z) + @prev2xdenom
	set @numerator = round(@decimal*@denominator,0)
	set @prev2xdenom = @temporary

	if ( ABS(@decimal-(convert(numeric(38,9),@numerator)/convert(numeric(38,9),@denominator))) < @accuracy )  OR ( @z = convert(int,@z)  )
	begin
		--Exit out of loop - assign fraction
		set @end = 1
		set @fraction = convert(varchar(10),@numerator) + '/' + convert(varchar(10),@denominator)
	end
	else
	begin
		goto fraction_loop
	end

select @fraction fraction