query related to convert function

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 2000 Forums

Transact-SQL (2000)

query related to convert function

Author

Topic

shubhada
Posting Yak Master

117 Posts

Posted - 2005-12-05 : 07:00:09

I want to convert ‘Code’ column having varchar data type into numeric. But I got the following error because ‘Code’ column have 39,21,39X, 45A values

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

My query is like this
Select * from PayerPricingMulti
Where convert (numeric, Code) = '39X'

Please tell me how I can overcome this problem

shubhada

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-12-05 : 07:35:28

You need to avoid all the characters from the Code column.. and then only you can apply the convert function..

What exactly you do u want to do .. ..??
and i guess there is no need to do convert for what u r doing..

Select * from PayerPricingMulti
Where Code= '39X'

This should work fine..

Sucess Comes to those who Believe in Beauty of their Dream..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-05 : 07:42:36

Why do you need the conversion?
Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

shubhada
Posting Yak Master

117 Posts

Posted - 2005-12-06 : 01:07:43

Column Code has varchar data type.
Data: 10,10.5A, 39,39X, 40,20,21,20X, 25

Suppose I want a Code between 10 to 25 so to find out a Code between this ranges, I write the following query

Select Code from table name
Where convert (number (11,5), Code) >=10 and convert (number (11,5), Code)<25

Result: I want all Code between 10 to 25
10,10.5A, 20,21,20X, 25

shubhada

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-12-06 : 02:10:05

if there is the Character feild in the column and u r trying to convert to the number you will always get error..

Check out this if its helps..

Declare @TableTmp Table
(
Data Varchar(10)
)
Insert @TableTmp
Select '10'
Union All Select '10.5A'
Union All Select '20'
Union All Select '21'
Union All Select '20X'
Union All Select '25'

Select * From @TableTmp Where Data Between '10' and '25'

Sucess Comes to those who Believe in Beauty of their Dream..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-06 : 02:10:53

Use this function to extract numbers only
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56713

and do query accordingly

Madhivanan

Failing to plan is Planning to fail

shubhada
Posting Yak Master

117 Posts

Posted - 2005-12-06 : 02:33:15

hi chiragkhabaria
your solution is working fine but I want to use a single query .Is it possible to fetch such a record through single query.

please help me.

shubhada

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-12-06 : 02:44:48

Single..query didnt got ..

On your table it will be somthing like this ..

Select * from PayerPricingMulti
Where Code Between '10' And '25'
??

Sucess Comes to those who Believe in Beauty of their Dream..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-06 : 02:59:20

>>Is it possible to fetch such a record through single query.

Isnt it a single query?

Select * From @TableTmp Where Data Between '10' and '25'

Madhivanan

Failing to plan is Planning to fail

shubhada
Posting Yak Master

117 Posts

Posted - 2005-12-06 : 03:16:05

Declare @TableTmp Table
(
Data Varchar(10)
)
Insert @TableTmp
Select '10'
Union All Select '10.5A'
Union All Select '20'
Union All Select '21'
Union All Select '20X'
Union All Select '25'

I have to put above login in procedure.ok...but i don't want this. query is written in VC++ code. it is not possible to write above code in VC++.

Suppose I write following query then
Select * from PayerPricingMulti
Where Code Between '10' And '25'
??

It not returns 20X, 19A such value. I want that value also.

shubhada

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-12-06 : 03:19:53

Ideally it should return..

Do you have any spaces or anything.. llike this ..

then you can try somthing like this

Select * from PayerPricingMulti
Where LTRim(RTrim(Code)) Between '10' And '25'

???

Sucess Comes to those who Believe in Beauty of their Dream..

shubhada
Posting Yak Master

117 Posts

Posted - 2005-12-06 : 04:10:40

I try between statement
Select Code from Table_Name
Where Code between ‘23’ and ‘123’

But above query not returning any data for range between ‘23’ and ‘123’

Code Column has following data
CR200
32.1
48.02
56.01
29.01
XDE
23
123
121

between is not working.

shubhada

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-12-06 : 04:21:02

Try This:

Select * from PayerPricingMulti
Where left(Code + space(12), 12) Between '10' + SPACE(10) And '25' + SPACE(10)

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-06 : 04:22:24

Why do you need this type of searching?

Declare @t table(data varchar(20))
Insert into @t select 'CR200' union all select '32.1' union all select '48.02'
union all select '56.01' union all select '29.01' union all select 'XDE'
union all select '23' union all select '123' union all select '121'
Select data from 
(
select cast(data as decimal(12,2)) as data from @t where data not like '%[a-z]%'
) T
where data between 23 and 123

Madhivanan

Failing to plan is Planning to fail

shubhada
Posting Yak Master

117 Posts

Posted - 2005-12-07 : 08:26:55

i have used following query

select Code,* from PayerPricingSingle
where Code in
(select distinct Code from PayerPricingSingle
where Code not like '%[A-Z]%' and Code not like '%[a-z]%'
and convert (float,Code) >= convert(float,'4') and
convert(float,Code) <= convert(float,'123')
)
and PayerPricingKey = 40

but it fails when Code column have any special character data like *,4*123,A*
please tell me how can exclude these special charater data.

shubhada

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-07 : 08:30:25

I already pointed out this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56713

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources