How to ignore varchar value in SQL table column

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

I have a table i.e.

create table tbl (id int, col1 varchar(50), col2 varchar(50),col3 varchar(50))insert into tbl values (1,'20','25','50')
insert into tbl values (2,'30','35','60')
insert into tbl values (3,'AB','AB','50')

I want to sum of three columns, for this I am using

select id, sum(convert(decimal,col1)+convert(decimal,col2)+convert(decimal,col3)) as total from tbl  group by id, col1, col2, col3

Now I want to ignore varchar values and see result like

id    total
     1     95
     2     125
     3     50

Please help someone...

What I have tried:

select id, sum(convert(decimal,col1)+convert(decimal,col2)+convert(decimal,col3)) as total from tbl  group by id, col1, col2, col3

Posted 27-Sep-17 20:26pm

Member 12245539

Updated 27-Sep-17 23:16pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Accepted Answer · 2017-09-27T21:07:00

Solution 1

try

select id, (col1+col2+col3) as Total
 from  (
		select
		id, 
		case  ISNUMERIC(col1) when 1  then CAST(col1 as decimal) else 0 end as col1 ,
		case  ISNUMERIC(col2) when 1  then CAST(col2 as decimal) else 0 end as col2 ,
		case  ISNUMERIC(col3) when 1  then CAST(col3 as decimal) else 0 end as col3 
		from tbl  group by id, col1, col2, col3  
      )a

Note: Please consider the points by Richard [^] in the comments section.

Posted 27-Sep-17 21:07pm

Karthik_Mahalingam

Updated 28-Sep-17 17:26pm

v6

Comments

Member 12245539 28-Sep-17 4:47am

5 star

Karthik_Mahalingam 28-Sep-17 5:10am

Thank you

Member 12245539 3-Oct-17 12:08pm

Boss
I you don't mind then may I ask a little bit??

I want to show col1, col2 & col3 also like
ID COL1 COL2 COL3 TOTAL
1 20 25 50 95
2 30 35 60 125
3 AB AB 50 50

Karthik_Mahalingam 3-Oct-17 23:10pm

select id,col1,col2,col3, (col1_s+col2_s+col3_s) as Total
 from  (
		select
		id, 
		col1,col2,col3,
		case  ISNUMERIC(col1) when 1  then CAST(col1 as decimal) else 0 end as col1_s ,
		case  ISNUMERIC(col2) when 1  then CAST(col2 as decimal) else 0 end as col2_s ,
		case  ISNUMERIC(col3) when 1  then CAST(col3 as decimal) else 0 end as col3_s 
		from tbl  group by id,col1,col2,col3   
      )a

Member 12245539 5-Oct-17 2:18am

Thanks

Karthik_Mahalingam 5-Oct-17 2:45am

welcome

Richard Deeming 28-Sep-17 11:12am

Be careful with ISNUMERIC - it considers all of the following strings to be "numeric":

* '-'
* '+'
* '$'
* '.'
* ','
* '\'

For SQL 2012 or later, TRY_PARSE[^] is a better option.

For earlier versions, column NOT LIKE '%[^0-9.]%' should be close enough.

Karthik_Mahalingam 28-Sep-17 23:23pm

Yes Richard, You have already told me the same in old post. I forgot :)
I will update my solution.