I am not sure if this is the right place, but here's my question:
with a field being varchar, can only the rows that are numeric be selected? For example-
ID Data1
1 don
2 jerry
3 3030
4 1234
5 susan
6 4321
Does SQL have an IsNumeric type function that can be used in the where clause?
Don
I have a field with State and Zip (ie; CA94526) which is a varchar field. I have lots of data that is invalid and need SELECT all records that the right(myfield,5) IS NOT Numeric. Can this be done?
Thanks!
how i convert varchar sal field to numeric in query
select sum(sal) from emp1
error:the sum or average aggregate operation cannot take a varchar data type as an argument.
Hi all,
I have a field defined as varchar(8) but this field should not contain any letters, needs to be only numbers. How can I validate the data if it contains only numbers? Any ideas?
Thanks,
Jannat.
I am putting a SELECT statement together where I need to evaluate a results field, to determine how the color indicator will show on a SSRS report. I am running into a problem when I try to filter out any non-numeric values from a varchar field, using a nested CASE statement.
For example, this results field may contain values of '<1', '>=1', '1', '100', '500', '5000', etc. For one type of test, I need a value of 500 or less to be shown as a green indicator in a report, and any value over that would be flagged as a red. Another test might only allow a value of 10 or less before being flagged with a red.
This is why I setup a CASE statement for an IndicatorValue that will pass over to the report to determine the indicator color. Using CASE statements for this is easier to work with, and less taxing on the report server, if done in SQL Server instead of nested SSRS expressions, especially since a variety of tests have different result values that would be flagged as green or red.
I have a separate nested CASE statement that will handle any of the values that contain ">" or "<", so I am using the following to filter those out, and then convert it to an int value, to determine what the indicator value should be. Here is the line of the script that is erring out"
case when (RESULT not like '%<%') or (RESULT not like '%>%') then
CASE WHEN (CONVERT(int, RESULT) between 0 and 500) THEN '2'
ELSE '0'
The message I am getting is: Conversion failed when converting the varchar value '<1' to data type int.
I thought a "not like" statement would not include those values for converting to an int, but that does not seem to be working correctly. I did also try moving the not to show as "not RESULT like", and that did not change the message.
How I can filter out non-numeric values before converting the rest of the varchar field (RESULT) to int, so that it is only converting actual numbers?
I am trying to setup an indicator value for an SSRS report to show green and red values on a report, based on the NRESULT value. The problem I am facing is that I have several different CASE statements that have the same logic, and they are processing just fine. NRESULT is a decimal field, so no conversion should be necessary. I do not know why I am getting the "Arithmetic overflow error converting varchar to data type numeric." error message.
Below is the CASE statement where the error is occurring. It is in the part of the ELSE CASE. The first CASE works just fine when the ELSE CASE is commented out. If I also change the ELSE CASE statement to say "else case when LEFT(NRESULT,1) = '-' then '0'", then it processes fine, too, so it has to be something I am missing something in the check on negative values. I do need the two checks, one for positive and one for negative values, to take place.
case when LEFT(NRESULT,1) <> '-' then --This portion, for checking positive values, of the CASE statement works fine.
CASE WHEN LEFT(ROUND(NRESULT,2),4) between 0.00 and 0.49 THEN '2' --Green
ELSE CASE WHEN LEFT(ROUND(NRESULT,2),4) > 0.49 THEN '0' --Red
ELSE '3' --White
END
END
else case when LEFT(NRESULT,1) = '-' then --This portion, for checking negative values, of the CASE statement is producing the conversion error message.
[code]....
I checked the NRESULT field, and there are not any NULL values in there, either.
Hi all,I've set up a page with a gridview, and I'm trying to create a query based on three parameters and to display all records when the page loads.I have created the first two parameters no problem, but I'm having problems with the last one.The parameter is to be populated by a dropdownlist, where the selectedvalue is numeric. I have done a search and found that I can't use the '%' wildcard as it is for a string data type.I have read somewhere that to get around this I can use the CHAR() function to convert to a character, I have tried this without success.When creating the ControlParameter the datatype gets set to string, which I think is not working becuase the input has to be an integer for it to conver to char, am I right?This is the query, and it is the @Application parameter that I'm having this problem with. SELECT dbo.Issue.IssueID, dbo.Issue.ReportedBy, dbo.Issue.ShortDescription, dbo.Issue.DateReported, dbo.Issue.Status, dbo.Priority.Description AS Priority,
dbo.Application.Application
FROM dbo.Issue INNER JOIN
dbo.Priority ON dbo.Issue.Priority = dbo.Priority.PriorityCode INNER JOIN
dbo.Application ON dbo.Issue.Application = dbo.Application.ApplicationID
WHERE (dbo.Issue.Status LIKE '%' + @Status) AND (dbo.Issue.AssignedTo = @AssignedTo) AND (CHAR(dbo.Application.ApplicationID) LIKE '%' + @Application)
ORDER BY dbo.Priority.PriorityCodeHas anyone managed to do this another way?Thanks
Need to know if the varchar datatype field will ingore leading zeros when compared with numeric datatype ?
create table #temp
(
code varchar(4) null,
id int not null
)
insert into #temp
[Code] .....
Does anyone know of a way to execute sql code from a dynamically builttext field?Before beginning, let me state that I know this db architecture isbuilt solely for frustration and I hope to make it better soon.Unfortunately, there is never a non-crucial time in which we can do anupgrade, so we are stuck for now.Point 1:There are multiple tables: students, courses, cross-referencestudent/courses, teachers, cross-reference teacher/courses, andothers.Point 2:Yearly archiving is done by appending a 2 digit year suffix to thetable and rebuilding the table structure for the current year. Thatis, each of the tables named above has an archive table for 1999,2000, 2001, etc. This leads to many tables and a big mess whenunioning them.Point 3:A history report is run by building a big query that unions each unionof tables (students, courses, etc) by year. This query has grown toobig for a varchar(8000) field. Actually, it's too big for 2 of them.Point 4:I don't want to write code to maintain any more varchar(8000) fieldsfor this query. It should be much more easily handled with atemporary table holding each bit of yearly archive data. I have builtthis and it works fine. I have also figured out how to pull the rowsfrom that table, concatenate them, and insert the resulting lump intoa text field in another table.Point 5:I haven't figured out how to grab the 5 or so records from that tableand execute them on their own. I figured I could grab them, put theminto a text field that's big enough to hold the whole query and thenselect and execute that text field. I've had no luck with that and Ihaven't had any luck finding any references that might help me withthis problem. I keep thinking of nesting execute() calls, but thatdoesn't work.I'm open to questions, potential solutions, hints about different wayto approach the problem, anything.Many thanks in advance,Rick Caborn
View 7 Replies View RelatedI have a table of zip codes, some with Canadian zips. I'd like to takea zip code and search for nearby zips. For example:Dim theZip As Integer = textbox1.text....Parameter.Add(@ziplow, SqlDbType.Int, 5).Value = (theZip - 10)....Parameter.Add(@ziphigh, SqlDbType.Int, 5).Value = (theZip + 10)SELECT * from ZIPCODES where Cast(zip_code as Integer) BETWEEN @lowzipAND @highzipProblem is the letters in the Canadian records cannot be cast asintegers for this process. I get this error:Syntax error converting the varchar value '53151 1' to a column of datatype int.Is there a SQL query that can exclude if no cast can be made to anInteger?Thanks!Anton
View 5 Replies View RelatedI need to replace Access Val() functions with similiar function in sql.
i.e. Return 123 from the statement: SELECT functionname(123mls)
Return 4.56 from the satement: SELECT functionname(4.56tonnes)
Any one with ideas please
Thanks
George
I have a Log table which records items found on library shelves during a shelf scanning process. Each time a shelf is scanned all the items found are recorded in the log table and the ScanCount value is incremented (per shelf). This means that if I get the records with the highest available ScanCount value for each ShelfID, then I would have the most up to date picture of my library.
From this sample data I would like to return the latest rows (max scan count) for all shelves (which would be LogID: 7,8 (shelf A) 3,4 (Shelf B) 5,6 (Shelf C):
LogID ShelfID ScanCount ItemName
1 A 1 Dave
2 A 1 Alan
3 B 1 Mike
4 B 1 Andy
5 C 1 Mary
6 C 1 Mark
7 A 2 Sam
8 A 2 Will
I'm not sure how to do this - presumably with an inner select? Could someone help? thanks
Hi,
I have rows with a datetime column which spans many years. I am wondering is there a more simple way to select those rows where the datetime column is in a particular year. For example...
where datetime_column.year = '2007'
I know i can use the 'between' sql etc...but am wondering if theres something more straightforward im not aware of.
Thanks..
I cannot in the life of me understand what goes wrong here... In a webapplication (C#.NET) I traced an inability to retrieve existing records to SQL Server, where I cannot do the same either. The problem is that in the parameterized query, some fields can be null, and thus when the corresponding fields in the database record are null also, they should be selected. But this won't happen for some reason.
I wrote a test SQL statement that gives me the same bogus:
DECLARE @institution int, @collection int, @serialnr int, @subnr nvarchar(50)SET @institution = 1 SET @collection = 1SET @serialnr = 240 SET @subnr = NULLSELECT ID, Institution, Collection, SerialNumber, SubNumber, AccessionYear, Sagsnummer, DanekraeNr, TaxonIdentified, Stratigraphy, TypeStatus, PlacementRoom, PlacementCabinet, PlacementDrawer, UnitNotesFROM SpecimensWHERE (Institution = @institution) AND (Collection = @collection) AND (SerialNumber = @serialnr) AND (SubNumber = @subnr)
Now there is at least one row with corresponding fields values (1, 1, 240, null), but it won't be selected! What is wrong!?
I have a column that is set to VARCHAR 1 and want to change it to NUMERIC 5,2
Is this possible or am I barking up the wrong tree?
Cheers,
Gary
I have imported data from excel file. When data came to SQL table, the typeof AMOUNT column was varchar. I tried to convert and cast amount type ofamount column to number type but it does not allow me to convert.What is the best way of importing data into SQL and type stays the same asit was in excel file ?Or anyone has any better solution, please let me.Thanks.
View 4 Replies View RelatedHello All,
I am querying from a table which has all varchar values. I want to display the result in graph and hence would need the varchar to be converted to Decimal. But I am getting the error : Error converting data type varchar to Numeric. Here is the SELECT code: Can you please help me do this? I'll post the script in the next post in 10 minutes....
I need create a field to store tax rate. I need only 2 decimal points. I defined the field as decimal, precision=5 and scale=2. Does it mean that it can hold value from 0.00 to 999.99?
View 12 Replies View Relatedi have a huge stored procedure abt 500 lines..and i am calling this sp from an asp.net page...thn i got this error - error converting varchar to numeric - and am trying to debug...is there any way we can find out where the error is coming from...like aproxly which line number..etcor do i have to go through each line manually and see where i am doing the conversion....
thanks
Not sure how I can convert varchar to numeric values in SQL Server? Trying to do so in Design and get error:
Unable to modify table.
ODBC error: [Microsoft][ODBC Server Driver][SQL Server] Error converting data type varchar to numeric.
Any suggestions?
Im running a DTS package that converts data in a fixed width text file to SQL table. The package runs successfully, but when I go to dump 60,000+ records in the table I get this error:
"Error Converting data type varchar to numeric."
Is there a way that I can isolate where the offending values are located so I can manually correct them?
Hi again all,I have a small issue. Here's an example dataset :F1 F2 F31 0.58 Hi2 0.70 Hello3 Fail Bye4 <Null> HiWhen I write this statement :SELECT SUM(CONVERT(DECIMAL(16,8),F2)) MySumFROM T1WHERE IsNumeric(IsNull(F2,'X'))=1I get "Cannot convert a Varchar value to Numeric" error. From what Iunderstand, it somehow tries to convert to a decimal(16,8) BEFORE filteringthe nulls and the non-numeric out. (Keep in mind that the actual table hasover 1.5Million records).Any idea on how to get around that ?Thanks,Michel
View 1 Replies View RelatedI am trying to change a value in a table. For instance the value is now 3. If I try to change the value to say 2 or 4 I get and error converting data type varchar to numeric. So wazzagoingon?
View 6 Replies View Related
Hi,
Can we insert varchar values to Decimal(9,2)/numeric(9,2) fields?
I've a temp table with varchar values. I checked using isnumeric and all the values are numeric. But when I do the insert, it fails.
I was able to insert the following values:
000290165
000501075
000290165
000326314
But NOT the following:
010773474
All the values are 9 digits only.......
How can I convert this kind of numbers?
I need to convert all the 9 digit numbers to XXXXXXX.xx format.
(7digits.2 digits)
Thanks,
Siva.
when i execute my store procedure it give this error.
USE [CWMNEW]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[BOQ]
SELECT'Return Value' = @return_value
GO
[code]....
I have a varchar(len=9) field that I want to cast as numeric(9,2). Some of the field values are null but some have valid values (ie 1.00 or .05).
I am selecting data from one table and then updating a different table.
I have a select statement within an insert statement and then an update statement. I've tried a cast statement but not successfully. I get the following error.
[Execute SQL Task] Error: Executing the query "Exec sp_ESTLoadPOData" failed with the following error: "Error converting data type varchar to numeric.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Hi All,
I have 2 varchar fields on MS 2005 table
First field is date and format is 080118(YYMMDD)
and second is salary field like 00002000(positive) and 00002000- (negative)
how can I move them to date and numeric fields on another table....
thanks
DB is SQL 2000 and I'm trying to convert a field datatype from varchar to numeric where the data is hours:minutes:seconds (05:52:12), but I run into errors with EM and scripts that the db cannot convert those datatypes. I've tried the cast function with same results ... any solutions?? I need to run SUM(), AVG(), etc, against the data and can't with VARCHAR type.
Thanks all
-Greg
I have a sql code that I am having some difficulty with. All I'm trying to do is get each department and sum the amount by month based on the date in the table. I am getting "error converting data type varchar to numeric".
Code:
select DEPARTMENT,
CASE
WHEN ((DATE >= '06/01/2014') AND (DATE <= '06/30/2014')) THEN (cast(sum(Amount) as decimal(10,2)))
ELSE '' END AS 'JUNE',
CASE
WHEN ((DATE >= '07/01/2014') AND (DATE <= '07/30/2014')) THEN (cast(sum(Amount) as decimal(10,2)))
ELSE '' END AS 'JULY'
from L27_PHAROS_DETAIL_DATA
WHERE Department = 'TECHNOLOGY SERVICES'
GROUP BY DEPARTMENT, DATE
ORDER BY DEPARTMENT
Hi Group:
I try to JOin to tables: Enterprise has a decimal(5,3) and COHIER has varchar 5. All I am getting is an error on converting data. How can I fix this problem.
SELECT a.Security_Value, b.Enterprise_Number, b.Enterprise_Description
FROM dbo.COHIER a INNER JOIN
dbo.Enterprise b ON a.Security_Value = b.Enterprise_Number
Thanks!!!!!!
Select SchoolNumber + ' ' + ShortSchoolName as SchoolList from tblSchoolsSubset
This is my simple sql statement which generates the error in the subject line.
SchoolNumber is type numeric and ShortSchoolName is nvarchar
There are only numbers in SchoolNumber.
Yes again a topic like this, I couldn't get an answer for my problem so I started a new topic.
Im getting this error for these lines:
$registreer = "INSERT INTO Gebruikerstelefoon (gebruikersnaam, telefoonnummer, volgnr) VALUES ('".$_SESSION['gebruikersnaam']."', '".$_POST["telefoonnummer"]."', '')";
mssql_query($registreer) or die("Fout bij toewijzen telefoonnummer.");
'telefoonnummer' (phonenumber) is nummeric, users can insert their phonenumber in a input field (text) and register along.. ( this is a second query for the column phonennumber) though this doesn't work..
Hi guys,
Been a while, also tried doing a search but no go.
Im getting this error in sql analyser:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric
Both queries work fine individually, im trying to append them together, with column columns as below:
SELECT PERIOD.STARTDATE,
PERIOD.DESCRIPTION,
COMMISSION_TXN.PERIODSEQ,
COMMISSION_TXN.SUPER_DISTRIBUTOR_CODE,
COMMISSION_TXN.PREMISE_CODE,
COMMISSION_TXN.DISTRIBUTOR_CODE,
COMMISSION_TXN.INTERNAL_EXTERNAL,
COMMISSION_TXN.SERVICE_ID,
COMMISSION_TXN.ACCOUNT_NUMBER,
COMMISSION_TXN.PROMOTION_CODE,
COMMISSION_TXN.PRODUCTDESCRIPTION,
COMMISSION_TXN.PRODUCTID,
COMMISSION_TXN.PROMOTION_NAME,
COMMISSION_TXN.EVENTTYPEID,
COMMISSION_TXN.MEASUREMENT_NAME,
Mobile_revenue = ('No Mobile revenue for txn level'),
COMMISSION_TXN.COMMISSION_AMOUNT,
product_id = ('No product id for txn level')
FROM COMMISSION_TXN INNER JOIN
PERIOD ON COMMISSION_TXN.PERIODSEQ = PERIOD.PERIODSEQ
union all
SELECT DISTINCT
PERIOD.STARTDATE,
PERIOD.DESCRIPTION,
TC_Mobile_Usage.PERIODSEQ,
Dealer_Hierarchy.SUPER_DISTRIBUTOR_CODE,
TC_Mobile_Usage.GENERICATTRIBUTE1 AS Dealer_premise_code,
Dealer_Hierarchy.DISTRIBUTOR_CODE,
internal_external = ('External'),
TC_Mobile_Usage.GENERICATTRIBUTE2 AS Service_Id,
account_number = ('none for txn file'),
promotion_code = ('Usage'),
Product_description = ('Usage'),
Product_id = ('Usage'),
promotion_name = ('Usage'),
TC_Mobile_Usage.EVENTTYPEID,
measurement_name = ('Usage'),
sum(TC_Mobile_Usage.VT_VALUE) AS Mobile_revenue,
sum(TC_Mobile_Usage.CONTRIBUTIONVALUE) AS Commission_Amount,
TC_Mobile_Usage.PRODUCTID
FROM TC_Mobile_Usage INNER JOIN
PERIOD ON TC_Mobile_Usage.PERIODSEQ = PERIOD.PERIODSEQ INNER JOIN
Dealer_Hierarchy ON TC_Mobile_Usage.GENERICATTRIBUTE1 = Dealer_Hierarchy.PARTICIPANTID
WHERE (NOT (TC_Mobile_Usage.PRODUCTID = 'iddv')) AND (NOT (Dealer_Hierarchy.POSITIONTYPEID LIKE 'MI %')) AND
(TC_Mobile_Usage.PERIODSEQ IN (107))--, 108, 110, 111, 112, 114))
group by PERIOD.STARTDATE,
PERIOD.DESCRIPTION,
TC_Mobile_Usage.PERIODSEQ,
Dealer_Hierarchy.SUPER_DISTRIBUTOR_CODE,
TC_Mobile_Usage.GENERICATTRIBUTE1,
Dealer_Hierarchy.DISTRIBUTOR_CODE,
TC_Mobile_Usage.GENERICATTRIBUTE2,
TC_Mobile_Usage.EVENTTYPEID,
TC_Mobile_Usage.PRODUCTID
Any suggestion on how to fix it?
Champinco