conver - like - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

conver - like

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-07-28 : 06:41:23

Hi,

In my sp, I am using

...
declare @Field1 float
...

select
...
AND (LEFT(f.Field1, LEN(@Field1)) = @Field1 OR @Field1 is NULL)
AND
...

Field1 is of type float in the database and should not be changed due to business needs...

The error I get is:
Error converting data type varchar to float.

It seems to be caused by the line above.

The goal is to take @Field1 such as 1.3 and return all the records that @Field1 has beginning with that value.
so the query should show the records which have Field1 of 1.3 or 1.32323 or 1.32, etc...
Should I be using a like in the above line?
if so, how do I do this please?

Thanks

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-07-28 : 06:46:24

AND (LEFT(cast(f.Field1 as varchar(10)), LEN(@Field1)) = @Field1 OR @Field1 is NULL)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-07-28 : 06:49:02

still get the same error.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-07-28 : 06:53:01

@Field1 is varchar or float?

If float Cast that too..

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-07-28 : 06:59:57

There seems to be a slight problem still.
That is if I assign @field1 = 1.30 then it returns the records with 1.33 or 1.30 or 1.35... whereas it should return only the records with 1.30 or 1.3021 or 1.3011, etc
Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-28 : 07:29:44

You should post your last used statement.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-28 : 07:31:46

Or try this
where column >= @field1 and column < @field1 + 0.1

No, you're never too old to Yak'n'Roll if you're too young to die.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-07-28 : 07:59:13

AND (convert(varchar(20), f.Field1) like convert(varchar(20),@Field1) + '%' OR @Field1 is NULL)
Any thoughts please?
Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-28 : 08:13:55

This is because 1.30 becomes 1.3
Have you read my other solution?

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-28 : 08:36:10

Work on numbers not varchars
Another method



declare @t table(f float)
declare @f float
set @f='1.3'
insert into @t
select 234.34 union all select 1.323489 union all select 1.36 union all select 1.5
select * from @t
where f like cast(@f as varchar(100))+'%'

Madhivanan

Failing to plan is Planning to fail

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-07-28 : 08:40:34

Thank you all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources