Query Problem

Syed Shahid Hussain

Hi I'm using the in query like

select * from table where col in ('1,2,3,4')

that is a simple query but i'm here in norway where they use (,) as a decimal point and (.) as comma. They when I use the query like this

select * from table where col in ('1.2')

it works fine but when I use query like this

select * from table where col in ('1.2.3.4.5')

It gives an error that error converting datatype...... What is the solution of tis hell. Thanks

Syed Shahid Hussain

Lost User

The solution is to use the decimal separator as a decimal separator, and not as a separator for arguments.

select * from table where col in ('1,2,3,4')

Now, if you want to use four decimals in that place, you'd use the English notation;

select * from table where col in ('1.2,2.9,3.83,4.0')

It's a good idea to keep the database culture-agnostic, and to format the decimal just before displaying. That means that each number is treated internally like we're in England, and that everything that gets displayed is formatted from that generic English form into the current culture.

I are Troll :)

Syed Shahid Hussain

Thanks Eddy But my problem is some thing different the col field is nvarchar and now i want to use it in IN query. There is no floating point here. then what will be the query it all values in the column are in integers. How can i put them in IN query. Any idea about that. Thanks

Syed Shahid Hussain

Lost User

Syed Shahid Hussain wrote:

How can i put them in IN query.

My apologies, there was another error that I missed. The arguments after the IN operator are summed within a varchar (a string), and SQL expects a list of items, not a string. It tries to convert the entire string to the datatype of "col". This code should run fine, even when the locale is Norsk;

SET LANGUAGE 'Norwegian'
DECLARE @myTable AS TABLE ( id INT)
INSERT INTO @myTable VALUES(1)
INSERT INTO @myTable VALUES(2)
INSERT INTO @myTable VALUES(3)
INSERT INTO @myTable VALUES(4)
SELECT * FROM @myTable WHERE id IN (1,3)

As you can see, the arguments in the select-list are separated by comma's (allowed, since they're not decimals) and there are no quotes (indicating multiple values to check against, as opposed to a single varchar value) That would translate to your query like this;

select * from table where col in (1, 2, 3, 4)

Or for decimals;

select * from table where col in (1.2, 3.89)

Make sure that the datatype of col matches the datatype of the values that you put after the IN operator :)

I are Troll :)

Syed Shahid Hussain

Thanks a lot its for you :rose:

Syed Shahid Hussain

Lost User

Thanks :)

Dave2909

Bah...at first glance I thought this was unanswered....in any case, drop the '' inside your IN and give it a list of separate values - sounds like that's already fixed ya right up... -Dave