Solved

How to check data in sql table

Posted on 2016-09-16
11
55 Views
Last Modified: 2016-09-16
Hello,

I am trying to convert a datatype of an column in sql table to money .But it fails due to some data which cannot be converted to money.How can  I check in the table which data is causing the failure.

Cheers
0
Comment
Question by:RIAS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41801382
It will help you if you provide us the table schema and the error message.
Also the code will be good to have so we can check it for you.
0
 

Author Comment

by:RIAS
ID: 41801388
I am using alter command to change the datatype to money .

ALTER TABLE [SNT]
                   ALTER COLUMN [Total]  money NULL

Error:
Msg 235, Level 16, State 0, Line 1
Cannot convert a char value to money. The char value has incorrect syntax.
The statement has been terminated.
0
 

Author Comment

by:RIAS
ID: 41801391
Just wanted to know which data is causing the failure.

Data is like:

606,759.71
79,040.00
9,922.98
350,680.23
16,997.84
295,522.10
50,000.00
60,000.00
9, 751.51
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 26

Expert Comment

by:Shaun Kline
ID: 41801394
Switching from CHAR to MONEY will be problematic because CHAR fields will end with added spaces so that all values have the specified character length.

Beyond that, you can query to find which values have invalid conversion values using the ISNUMERIC() function:
SELECT *
FROM SNT
WHERE ISNUMERIC(Total) <> 1

Open in new window

0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41801395
Better way to find it is running the following query:
SELECT *
FROM SNT
WHERE ISNUMERIC(Total)=0

Open in new window

This will return all rows that can't be converted.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41801396
Please use Try_Cast or Try_Convert or try_parse functions

TRY_PARSE
 
It converts string data type to target data type(Date or Numeric). For example, source data is string type and we need to covert to date type. If conversion attempt fails it returns NULL value.
 
Syntax: TRY_PARSE (string_value AS data_type [ USING culture ])
String_value – This is argument is source value which is NVARCHAR(4000) type.
Data_type – This argument is target data type either date or numeric.
Culture – It is an optional argument which helps to convert the value to in Culture format. Suppose you want to display the date in French, then you need to pass culture type as ‘Fr-FR’. If you will not pass any valid culture name, then PARSE will raise an error.
Examples
DECLARE @ fakeDate AS varchar(10);  
DECLARE @ realDate AS VARCHAR(10);  
SET @fakeDate = 'iamnotadate';  
SET @realDate = '13/09/2015;  
SELECT TRY_PARSE(@fakeDate AS DATE); --NULL  
SELECT TRY_PARSE(@realDate AS DATE); -- 2015-09-13  
SELECT TRY_PARSE(@realDate AS DATE USING 'Fr-FR'); -- 2015-09-13  
First query attempts to convert a non-date to date type, it fails and returns NULL value. Second query successfully converts to date type. Third query also successfully converts but in French forma t(Here it is not showing any difference you can use other culture to see the difference).

TRY_CONVERT

It converts value to specified data type and if conversion fails it returns NULL. For example, source value in string format and we need date/integer format. Then this will help us to achieve the same.

Syntax: TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )
Data_type - The datatype into which to convert. Here length is an optional parameter which helps to get result in specified length.
Expression - The value to be convert
Style - It is an optional parameter which determines formatting. Suppose you want date format like “May, 18 2013” then you need pass style as 111. More on style visit here.
Examples:
DECLARE @sampletext AS VARCHAR(10);  
SET @sampletext = '123456';  
DECLARE @ realDate AS VARCHAR(10);  
SET @realDate = '13/09/2015’;  
SELECT TRY_CONVERT(INT, @sampletext); -- 123456  
SELECT TRY_CONVERT(DATETIME, @sampletext); -- NULL  
SELECT TRY_CONVERT(DATETIME, @realDate, 111); -- Sep, 13 2015  
First query converts the text to integer successfully, but in second query the conversion fails and it returns NULL. Third query successfully converts string value to date type with specified formats.

TRY_CAST

It converts value to specified data type and if conversion fails it returns NULL. For example, source value in string format and we need it in double/integer format. Then this will help us in achieving it.
 
Syntax: TRY_CAST ( expression AS data_type [ ( length ) ] )
Expression - The source value which will go to cast.
Data_type - The target data type the source value will cast.
Length - It is an optional parameter that specifies the length of target data type.
Examples
DECLARE @sampletext AS VARCHAR(10);  
SET @sampletext = '123456';  
 
SELECT TRY_CAST(@sampletext AS INT); -- 123456  
SELECT TRY_CAST(@sampletext AS DATE); -- NULL  
Firstly, query converts the text to integer successfully, but in second query the conversion fails and it returns NULL.
1
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41801398
Putting a function on top of a column in a where clause will KILL the query performance as will always scan the table.
0
 

Author Closing Comment

by:RIAS
ID: 41801400
Thanks Vitor!
Special thanks to Pawan!
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41801403
@Rias - If my solution helped could you mark my solution as assisted one.? Thanks !
0
 

Author Comment

by:RIAS
ID: 41801407
Pawan,

Thanks.Your suggestion was to how to convert datatype.
But,my question was How can  I check in the table which data is causing the failure.

Cheers
1
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41801412
Fine. Thanks !
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
HIghlights of SSIS? 3 45
sql server string_split 4 30
Delete duplicates from SQL Server table 2 27
Query Result column name from data 16 14
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question