• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 875
  • Last Modified:

convert varchar to integer

hi there
how can i do this?
Data type is varchar 12

JP5DA03T    

JP5DA03T    

105DA53T  



105DA50T  


i want the results as



JP5DA03T    y

JP5DA03T    y

105DA53T  y



105DA50T n


if substr(tactic_code,6,1) >0 the y else n
0
nav29
Asked:
nav29
  • 8
  • 5
  • 4
  • +1
1 Solution
 
k_murli_krishnaCommented:
SELECT tactic_code, CASE WHEN substr(tactic_code,7,1) >0 the 'y' else 'n' END FROM MyTable;
0
 
k_murli_krishnaCommented:
Sorry it should be:
SELECT tactic_code, CASE WHEN substr(tactic_code,7,1) >'0' the 'y' else 'n' END FROM MyTable;

OR

SELECT tactic_code, CASE WHEN INTEGER(substr(tactic_code,7,1)) >0 the 'y' else 'n' END FROM MyTable;

0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi nav,

A couple of things come to mind.

SELECT item, case when substr (item, 1, 1) >= '0' and substr (item, 1, 1) <= '9' then 'n'
                              else case when substr (item, 2, 1) >= '0' and substr (item, 2, 1) <= '9' then 'n'
                                              else 'y'
                                     end
                              else 'y'
                     end
FROM ....

You can also use translate to remap the first two characters

SELECT item, case when translate (substr (item, 1, 2), '0000000000', '0123456789') = '00' then 'n' else 'y' end
FROM ...


Good Luck,
Kent
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
k_murli_krishnaCommented:
Your type 'the' carried on into my queries. It should be THEN 'y' instead of the 'y'.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi k_murli_kris,

Every version of DB2 that I'm familiar with throws an exception if you attempt to recast a non-numeric value to an integer.


Kent
0
 
nav29Author Commented:
gettin this error when running

SELECT tactic_code FROM aa where INTEGER(substr(tactic_code,6,1)) >0

TACTIC_CODE
------------
105AB53T    
105AB53T    
105AB53T    
105AB53T    
SQL0420N  Invalid character found in a character string argument of the
function "INTEGER".  SQLSTATE=22018

SQL0420N  Invalid character found in a character string argument of the function "INTEGER                                                               ".

Explanation
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Nav,

Yep.  You can't recast it to an integer to see if it is an integer.  (The thing that looks like a function call to INTEGER is really a recast.)

Try this one:

SELECT tactic_code , case when translate (substr (tactic_code , 1, 2), '0000000000', '0123456789') = '00' then 'n' else 'y' end
FROM aa;

Kent

0
 
nav29Author Commented:
Kemt when i run this sql
i do get results as follow

105AB53T = n  when it should be y
but for some it looks ok

like
JP5DA03T = y which is good
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi nav,

The statement should return 'n' if the first the characters are digits.  Looking at your first example, the query isn't really that simple, though.

What criteria do you use to determine if a 'y' or 'n' should be displayed?


Kent
0
 
nav29Author Commented:
criteria is if substr(tactic_code,6,1)) >0 the y else n
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi nav,

This is your original list.  None of the items are numeric across the first 6 characters.

JP5DA03T    y
JP5DA03T    y
105DA53T   y
105DA50T   n

So with that in mind, is the criteria "if the first character is numeric and the string is >'1'"?

If so,

SELECT
  tactic_code,
  case when substr (tactic_code , 1, 1) >= '1' and substr (tactic_code, 1, 1) <= '9' then 'n' else 'y' end
FROM aa;

That will catch everything that starts with 1,2,3,4,5,6,7,8,9 but not 0 or any non-digit.


Kent
0
 
nav29Author Commented:
well just test it
105AB53T shows me n
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Sorry -- coded the flag backwards.  :(

SELECT
  tactic_code,
  case when substr (tactic_code , 1, 1) >= '1' and substr (tactic_code, 1, 1) <= '9' then 'y' else 'n' end
FROM aa;
0
 
nav29Author Commented:
Now
JP5DA03T is showing n :(
0
 
Kent OlsenData Warehouse Architect / DBACommented:

Jeepers.  I just completely changed the rules.  I was testing the first character, not the sixth....


SELECT
  tactic_code,
  case when substr (tactic_code , 1, 1) >= '1' and substr (tactic_code, 6, 1) <= '9' then 'y' else 'n' end
FROM aa;


0
 
k_murli_krishnaCommented:
You are right, kent, but I gave a solution without trying to cast/convert to an INTEGER:

SELECT tactic_code, CASE WHEN substr(tactic_code,7,1) >'0' then 'y' else 'n' END FROM MyTable;
nav29: Your rule if substr(tactic_code,6,1) >0 the y else n does not match for 6th character but matches for 7th character, hence my rule.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi k_murli_kris...

I wound up going back to the original post where the author was using substr (item, 6, 1) and assumed that that was the correct position.

Simply testing for keychar >= '1' may not be enough.  In the ASCII sequence, all of the alphabetics map to values greater than '9' so simply testing for >= '1' returns TRUE for alphabetic characters as well as the non-zero digits.  Punctuation is split in the table with about half having a value < '1' and the other half having a value > '9'.  Testing for keychar >= '1' will produce seemingly random results if the character is punctuation as there won't be a consistent 'y' or 'n' returned.

Also, in DB2 string position is relative 1.  The first character is substr (item, 1, 1) so the sixth would be substr (item, 6, 1).


Kent

Kent
0
 
tliottaCommented:
nav29:
Well, I still don't get this part:

i want the results as

JP5DA03T    y
JP5DA03T    y
105DA53T  y

105DA50T n

if substr(tactic_code,6,1) >0 the y else n
It hasn't been made clear if [ 105DA50T n ] is correct. After everything that's been said, substr(tactic_code,6,1)='5' which should give 'y' rather than 'n'. Even substr(tactic_code,7,1)='0' which should also give 'y' rather than 'n'.
Until the question is clarified, any answer will be from luck.

Please explain why [ 105DA50T n ] is correct. Please show which character position should be tested.

The only thing I can think of is that a [ BETWEEN '1' AND '9' ] is desired for the 7th character position. The term 'integer' would then be used to mean something like 'non-zero integer'. And that would explain the problem with [ JP5DA03T is showing n :( ].

Tom

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 8
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now