Why does the following Query in SQL return true?? I expected it to be false as it cannot be converted into an int or numeric value  select ISNUMERIC(',')  return 1???  select ISNUMERIC('0,1,2')  select ISNUMERIC(',,,')   also returns 1  What could I do for strict numeric checking in SQL?
12285
Why would the following query return "Error converting data type varchar to bigint"?  Doesn't IsNumeric make the CAST safe?  I've tried every numeric datatype in the cast and get the same "Error converting..." error.  I don't believe the size of the resulting number is a problem because overflow is a different error.  The interesting thing is, in management studio, the results actually show up in the results pane for a split second before the error comes back.  SELECT CAST(myVarcharColumn AS bigint)   FROM myTable   WHERE IsNumeric(myVarcharColumn) = 1 AND myVarcharColumn IS NOT NULL   GROUP BY myVarcharColumn   Any thoughts?
10301
I was wondering if there was an easy way in SQL to convert an integer to its binary representation and then store it as a varchar.  For example 5 would be converted to "101" and stored as a varchar.
684
Is there any way to check whether an integer is a member of a list of integer numbers and return a boolean?   I found this neat solution,  that does a great job. It checks whether a string exists in a list of strings:   \documentclass{article} \usepackage{xstring} \newcommand\IfStringInList[2]{\IfSubStr{,#2,}{,#1,}} \begin{document}     \IfStringInList{Paul}{George,John,Paul,Ringo}{True}{False} \end{document}    However, it does return a string not a boolean "true" or "false" and caused me some difficulty which is posted .    Let's call the macro I would like to have \ISMEMBER. My goal is to use \ISMEMBER and examine whether an integer is in the list and depending on whether it is or not, perform some tasks, as an example:  \fpeval{ \ISMEMBER{1}{1,2,3,4,5} ? \MACRO_FOR_MEMBERS : \MACRO_FOR_NON_MEMBERS }   Is such functionality possible?
57093
I think there's a lot for me to learn about data types. Why this happens  double result = ((3/8)*100).ToString();   it gives zero .. should be 37,5 ... :(
28318
I'm trying to format numbers. Examples:  1     =&gt; 1 12    =&gt; 12 123   =&gt; 123 1234  =&gt; 1,234 12345 =&gt; 12,345   It strikes as a fairly common thing to do but I can't figure out which filter I'm supposed to use.  Edit: If you've a generic Python way to do this, I'm happy adding a formatted field in my model.
56109
I'm curious as to whether or not there is a real difference between the money datatype and something like decimal(19,4) (which is what money uses internally, I believe).  I'm aware that money is specific to SQL Server. I want to know if there is a compelling reason to choose one over the other; most SQL Server samples (e.g. the AdventureWorks database) use money and not decimal for things like price information.    Should I just continue to use the money datatype, or is there a benefit to using decimal instead? Money is fewer characters to type, but that's not a valid reason :)
36557
I am curious as to why an implicit cast fails in...  int? someValue = SomeCondition ? ResultOfSomeCalc() : null;   and why I have to perform an explicit cast instead  int? someValue = SomeCondition ? ResultofSomeCalc() : (int?)null;   It seems to me that the compiler has all the information it need to make an implicit casting decision, no?
6181
Something very odd is happening here.   I have a query that looks like this.  SELECT CAST(FT.DOP AS SMALLINT) FROM TRACKING_DATA WHERE date &gt; @mydate and identifier = 0000000000   When run as a raw query it returns data just fine.  When i place it in a stored procedure that alters the where clause it throws this error.  Msg 244, Level 16, State 2, Procedure myprocedure, Line 107 [Batch Start Line 2] The conversion of the varchar value '58629' overflowed an INT2 column. Use a larger integer column.   So here is the oddity. I go through all the possible data for that where clause with a query like this.  SELECT DISTINCT DOP FROM TRACKING_DATA where identifier = 000000000000   and  SELECT DISTINCT CAST(DOP AS smallint) FROM TRACKING_DATA where identifier = 000000000000   And this is what i get back.  17 12 9 19 8 14 6 16 11 13 7 10 0 18 5 15 4   Nowhere does it have anything remotely too large for a SMALLINT. So i thought, ok maybe its a non-printable ASCII character. But i can't find any.  I'm a little perplexed at the moment. It runs find as a raw query, explodes as a procedure and all possible data based on the where is valid. My only suspicion is that the query plan is doing something odd with filtering or maybe runs with different validation when run as a procedure.
36370
This is probably a simple answer but I can't find it.  I have a table with a column of integers and I want to ensure that when a row is inserted that the value in this column is greater than zero.  I could do this on the code side but thought it would be best to enforce it on the table.  Thanks!  I was in error with my last comment all is good now.
16869
I have some data tables that where imported in from a flat file source so everything is varchar(max) except FileRecordID which is a int that hold the row number the data came from in the flat file source (and is the primary key of the imported table.)  The following query works perfectly  SELECT *   FROM [CLIENT]   left outer join   (     select cast(ENTRY_CODE as int) as ENTRY_CODE, ENTRY_DESCRIPTION     from [ENTRY]     inner join [TABLE] on [ENTRY].ENTRY_TABLE = [Table].FileRecordID     where ENTRY_RECD = 'A' and TABLE_RECD = 'A' and TABLE_CODE = 'CLTDISC' and ISNUMERIC(ENTRY_CODE) = 1   ) as discounts on CLIENT_CLASS = ENTRY_CODE   where Client_recd = 'A'   --and client.[FileRecordID] = 10607   I get the exact number of rows I was expecting and everything is fine. However if I uncomment that last line so I only show the record from the client data table who has  client.[FileRecordID] = 10607 it reports to me      Msg 245, Level 16, State 1, Line 1   Conversion failed when converting the varchar value 'BK/TN' to data type int.   The problem is from cast(ENTRY_CODE as int) as ENTRY_CODE in the first outer join. ENTRY can contain text in the ENTRY_CODE column, however all records that have ENTRY_TABLE set to the line number from TABLE where TABLE_CODE = 'CLTDISC' will always be numbers only. I perform the cast as ENTRY_CODE may or may not have leading 0's and spaces so I am trying to get them to be well formed.  What appears to me to be happening is adding client.[FileRecordID] = 10607 to the outer query causes the where clause not to be evaluated before the cast to int in the inner query.  I have tried things like adding and ISNUMERIC(ENTRY_CODE) = 1 to both the inner and outer queries but it has no affect.   How do I fix this? Can anyone explain what is happening so I can understand how to avoid this in the future?   Non filtered query planed execution   Filtered (non working) query planned execution  Notice the reversed order of the Compute Scalar and the Filter    Other things I have tried that do not work:   Move TABLE_CODE = 'CLTDISC' in to the on clause in the inner join. Added OPTION (FORCE ORDER) query hint to the query.     Update:  but it is very "hackish" IMHO, I really would still like a explanation of how to avoid things like this in the future and how to do this correctly.  SELECT *   FROM [CLIENT]   left outer join   (     select ENTRY_CODE, ENTRY_DESCRIPTION     from [ENTRY]     inner join [TABLE] on [ENTRY].ENTRY_TABLE = [Table].FileRecordID     where ENTRY_RECD = 'A' and TABLE_RECD = 'A' and TABLE_CODE = 'CLTDISC' and ISNUMERIC(ENTRY_CODE) = 1   ) as discounts on CLIENT_CLASS = REPLACE(LTRIM(REPLACE(ENTRY_CODE, '0', ' ')), ' ', '0')   where Client_recd = 'A'   and client.[FileRecordID] = 10607
2827
What's the cleanest, most effective way to validate decimal numbers in JavaScript?  Bonus points for:   Clarity. Solution should be clean and simple. Cross-platform.   Test cases:  01. IsNumeric('-1')      =&gt; true 02. IsNumeric('-1.5')    =&gt; true 03. IsNumeric('0')       =&gt; true 04. IsNumeric('0.42')    =&gt; true 05. IsNumeric('.42')     =&gt; true 06. IsNumeric('99,999')  =&gt; false 07. IsNumeric('0x89f')   =&gt; false 08. IsNumeric('#abcdef') =&gt; false 09. IsNumeric('1.2.3')   =&gt; false 10. IsNumeric('')        =&gt; false 11. IsNumeric('blah')    =&gt; false
57572
I normally use the following idiom to check if a String can be converted to an integer.  public boolean isInteger( String input ) {     try {         Integer.parseInt( input );         return true;     }     catch( Exception e ) {         return false;     } }   Is it just me, or does this seem a bit hackish?  What's a better way?    See my answer (with benchmarks, based on the  by ) to see why I've reversed my position and accepted  to this problem.  I think this original code will be used by most people because it's quicker to implement, and more maintainable, but it's orders of magnitude slower when non-integer data is provided.
115109
Consider this table in mysql  create table numbers (number int); insert into numbers values (3), (2), (9); select * from numbers;  +--------+ | number | +--------+ |      3 | |      2 | |      9 | +--------+   Is there a simple query to generate a table with the following columns   The numbers from 1 till 10 1 if the number is present in the table numbers, and 0 otherwise   I guess you have to create a sequence of numbers in order to do this. If possible, I want to create such a sequence without storing it in the database.  Related question: is there a select query that generates a sequence of numbers from 1 till 10 (or 100 or 1000)?
122950
I noticed this while doing some performance testing recently. When I insert a value into a column that will require an implicit conversion (e.g. bigint into nvarchar), I get a warning:      Type conversion in expression (CONVERT_IMPLICIT(nvarchar(50),[tempdb].[dbo].[#MyFunIntTable].[EvenCoolerColumn],0)) may affect "Cardinality Estimate" in query plan choice.   Being a concerned citizen, I checked all of the obvious suspects and eventually dug into the XML to confirm that it was actually warning about the insert into the table. The problem is, I can't figure out why this would ever affect cardinality estimates. If I were doing this in a join or somewhere with a little more logic it would make sense, but there shouldn't be a cardinality estimate mismatch for the actual insert operation, right?  I noticed that this happened when it was more than just a trivial query - as soon as more than one value is inserted, or we're pulling a value from a table, we hit this.  This question has attracted some potential duplicates, including:       I think it is different from these questions because I'm literally not doing anything with this column. I'm not using it in a filter, or a sort, or a grouping, or a join, or in a function - any of these things makes the scenario more complicated. All I'm doing is inserting a bigint into a nvarchar, which should never impact a meaningful cardinality estimate that I can think of.  What I'm specifically looking for out of an answer is:   An explanation of why I get this warning despite nothing of interest going on - is it just that SQL Server will be conservative and report even when it won't affect plan choice? What cardinality estimate is actually at risk here, and what operation would change based off of inaccuracies in that cardinality estimate? Is there a scenario where this could affect plan choice? Obviously if I start joining or filtering on the converted column it could, but as-is? Is there anything that can be done to prevent it from warning, besides changing data types (assume this is a requirement of how the data models interact)   I recreated it with the below simple example ()  DROP TABLE IF EXISTS #MyFunStringTable; DROP TABLE IF EXISTS #MyFunIntTable;  CREATE TABLE #MyFunStringTable (   SuperCoolColumn nvarchar(50) COLLATE DATABASE_DEFAULT NULL );  CREATE TABLE #MyFunIntTable (   EvenCoolerColumn bigint NULL );  INSERT INTO #MyFunIntTable ( EvenCoolerColumn ) VALUES ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 );  INSERT INTO #MyFunStringTable ( SuperCoolColumn )   SELECT EvenCoolerColumn     FROM #MyFunIntTable;  INSERT INTO #MyFunStringTable ( SuperCoolColumn ) VALUES ( 1 );  INSERT INTO #MyFunStringTable ( SuperCoolColumn ) VALUES ( 1 ), ( 2 );  INSERT INTO #MyFunStringTable ( SuperCoolColumn )   SELECT 1;  INSERT INTO #MyFunStringTable ( SuperCoolColumn ) SELECT 1 UNION ALL SELECT 2;  INSERT INTO #MyFunStringTable ( SuperCoolColumn )   SELECT 1     FROM #MyFunIntTable;
39428
I'm looking to find records in a table that match a specific number that the user enters.  So, the user may enter 12345, but this could be 123zz4-5 in the database. I imagine something like this would work, if PHP functions worked in MySQL. SELECT * FROM foo WHERE preg_replace(&quot;/[^0-9]/&quot;,&quot;&quot;,bar) = '12345'  What's the equivalent function or way to do this with just MySQL? Speed is not important.
36213
I'm a bit stuck on this. Basically I want to do something like the following SQL query in LINQ to SQL: SELECT f.*  FROM Foo f WHERE f.FooId IN (     SELECT fb.FooId     FROM FooBar fb     WHERE fb.BarId = 1000 )  Any help would be gratefully received.
50537
I have PG server (9.0.4) installed on Debian x86. In one of my tables I use numeric(6,1):  CREATE TABLE mytable(name text, numfield numeric(6,1))   Also index created for this field:  CREATE INDEX mytable_numfield_idx ON mytable USING btree (numfield);   Everything is going fine if I use this field with real numeric value. As I see the query uses the index:  EXPLAIN ANALYZE SELECT * FROM kodiall WHERE kodgo=123.0      Index Scan using mytable_numfield_idx on mytable  (cost=0.00..8.27 rows=1   width=193) (actual time=0.085..0.087 rows=1 loops=1)      Index Cond: (numfield = 123.0)      Total runtime: 0.131 ms   But the index is ignored for some reason if I use 0.0 or NULL as condition value:  EXPLAIN ANALYZE SELECT * FROM kodiall WHERE numfield=0.0 --EXPLAIN ANALYZE SELECT * FROM kodiall WHERE numfield=NULL      Seq Scan on mytable  (cost=0.00..57.80 rows=1080 width=193) (actual   time=0.033..1.853 rows=1088 loops=1)      Filter: (numfield = 0.0)   What wrong here? Is 0.0 not a numeric value?  In real query I use LEFT JOIN with numfield and values in joined table can be NULL.