summing alpha columns

Question

summing alpha columns

fmarler

SSC Rookie

Points: 32
More actions
November 17, 2015 at 10:08 am

#309696

Wanting to do something like:
Select * from tbl
having cast(isnull(Column1,0) as int) + cast(isnull(Column2,0) as int) + cast(Column3 as int) > 0
my issue is all 3 columns are varchar and can have all alpha characters
any help with this would be great

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

fmarler (11/17/2015)
Wanting to do something like:
Select * from tbl
having cast(isnull(Column1,0) as int) + cast(isnull(Column2,0) as int) + cast(Column3 as int) > 0
my issue is all 3 columns are varchar and can have all alpha characters
any help with this would be great

Well, hard to know what to do when you haven't told us what you are trying to accomplish or provided DDL, sample data, and expected results.

fmarler SSC Rookie Points: 32 More actions · Answer 2

I have a table that has 15 varchar columns they all contain data like "ABC" or are null I am trying to find if any of these column have data in them.

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 3

ZZartin

SSC-Dedicated

Points: 30894

November 17, 2015 at 10:26 am

#1840415

SELECT * FROM Tbl WHERE column IS NOT NULL?

fmarler SSC Rookie Points: 32 More actions · Answer 4

SELECT

[transaction_detail_id]

,[transaction_code]

,[transaction_short_desc]

,[transaction_desc]

[highlight=#ffff11] ,[trans_char_1]

,[trans_char_2]

,[trans_char_3]

,[trans_char_4]

,[trans_char_5]

,[trans_char_6]

,[trans_char_7]

,[trans_char_8]

,[trans_num_1]

,[trans_num_2]

,[trans_num_3]

,[trans_num_4]

,[trans_curr_1]

,[trans_curr_2]

,[trans_curr_3]

,[trans_curr_4]

,[trans_date_1]

,[trans_date_2]

,[trans_date_3]

,[trans_date_4]

,[trans_memo_1][/highlight]

,[active]

[highlight=#ffff11] ,[speedlim]

,[speedact]

,[citationDt1]

,[citationDt2]

,[citationDt3]

,[citationDt4]

,[citationDt5]

,[citationDt6]

,[citationDt7]

,[convictionDt1]

,[convictionDt2]

,[convictionDt3]

,[convictionDt4]

,[convictionDt5]

,[convictionDt6]

,[convictionDt7]

,[convCd1]

,[convCd2]

,[convCd3]

,[convCd4]

,[convCd5]

,[convCd6]

,[convCd7]

,[waiver]

,[statute]

,[convoffense]

,[accident]

,[diversion]

,[diversiondt]

,[viol_type]

,[plea]

,[findingby]

,[conv_date]

,[verdict]

,[amended]

,[bondforfeit]

,[jaildays]

,[suspdays]

,[revdays]

,[restdays]

,[orders]

,[dmvcode][/highlight]

,[dispo_code_id]

FROM [HalsteadMCS].[dbo].[tblTransactionDetail]

I need to see if there is data in any of the highlighted fields. Data is always alphanumeric like "abc1" or "abc" or "123"

I am using vb 6 for an application if there is any data in the varchar columns highlighted I need to use this table to populate a form otherwise I need to use another table.

Sean Lange SSC Guru Points: 286573 More actions · Answer 5

fmarler (11/17/2015)
SELECT
[transaction_detail_id]
,[transaction_code]
,[transaction_short_desc]
,[transaction_desc]
[highlight=#ffff11] ,[trans_char_1]
,[trans_char_2]
,[trans_char_3]
,[trans_char_4]
,[trans_char_5]
,[trans_char_6]
,[trans_char_7]
,[trans_char_8]
,[trans_num_1]
,[trans_num_2]
,[trans_num_3]
,[trans_num_4]
,[trans_curr_1]
,[trans_curr_2]
,[trans_curr_3]
,[trans_curr_4]
,[trans_date_1]
,[trans_date_2]
,[trans_date_3]
,[trans_date_4]
,[trans_memo_1][/highlight]
,[active]
[highlight=#ffff11] ,[speedlim]
,[speedact]
,[citationDt1]
,[citationDt2]
,[citationDt3]
,[citationDt4]
,[citationDt5]
,[citationDt6]
,[citationDt7]
,[convictionDt1]
,[convictionDt2]
,[convictionDt3]
,[convictionDt4]
,[convictionDt5]
,[convictionDt6]
,[convictionDt7]
,[convCd1]
,[convCd2]
,[convCd3]
,[convCd4]
,[convCd5]
,[convCd6]
,[convCd7]
,[waiver]
,[statute]
,[convoffense]
,[accident]
,[diversion]
,[diversiondt]
,[viol_type]
,[plea]
,[findingby]
,[conv_date]
,[verdict]
,[amended]
,[bondforfeit]
,[jaildays]
,[suspdays]
,[revdays]
,[restdays]
,[orders]
,[dmvcode][/highlight]
,[dispo_code_id]
FROM [HalsteadMCS].[dbo].[tblTransactionDetail]
I need to see if there is data in any of the highlighted fields. Data is always alphanumeric like "abc1" or "abc" or "123"
I am using vb 6 for an application if there is any data in the varchar columns highlighted I need to use this table to populate a form otherwise I need to use another table.

Ouch this is going to be painful. You will have to first get the data that IS a number into a temp table so you can do calculations on it.

You desperately need to read up on normalization. This data structure is very painful to work with. Anytime you have repeating columns you should spin that data off to a second table.

If you really want some help with this you need to provide more details. Please see the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

fmarler SSC Rookie Points: 32 More actions · Answer 6

I know. Not the way I would have designed it but I inherited this and am stuck using it.

Sean Lange SSC Guru Points: 286573 More actions · Answer 7

fmarler (11/17/2015)
I know. Not the way I would have designed it but I inherited this and am stuck using it.

Understand. Did you see this part?

If you really want some help with this you need to provide more details. Please see the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

If you were working on a 2012+ version, you could use try_parse, try_convert or try_cast.

Otherwise, you need to do it manually.

SELECT *

FROM tbl

WHERE CASE WHEN Column1 NOT LIKE '%[^0-9]%' THEN Column1 END > 0

OR CASE WHEN Column2 NOT LIKE '%[^0-9]%' THEN Column2 END > 0

OR CASE WHEN Column3 NOT LIKE '%[^0-9]%' THEN Column3 END > 0

This will do an implicit conversion to int only when the columns contain only digits. For an explanation on how the condition works, read the following article: http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2