Solved

convert varchar to integer

Posted on 2009-04-09
18
861 Views
Last Modified: 2012-05-06
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
Comment
Question by:nav29
  • 8
  • 5
  • 4
  • +1
18 Comments
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 24109364
SELECT tactic_code, CASE WHEN substr(tactic_code,7,1) >0 the 'y' else 'n' END FROM MyTable;
0
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 24109374
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
 
LVL 45

Expert Comment

by:Kdo
ID: 24109381
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
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 24109388
Your type 'the' carried on into my queries. It should be THEN 'y' instead of the 'y'.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 24109402
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
 

Author Comment

by:nav29
ID: 24109545
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
 
LVL 45

Expert Comment

by:Kdo
ID: 24109602
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
 

Author Comment

by:nav29
ID: 24109778
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
 
LVL 45

Expert Comment

by:Kdo
ID: 24110418
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:nav29
ID: 24110535
criteria is if substr(tactic_code,6,1)) >0 the y else n
0
 
LVL 45

Expert Comment

by:Kdo
ID: 24110584
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
 

Author Comment

by:nav29
ID: 24110809
well just test it
105AB53T shows me n
0
 
LVL 45

Expert Comment

by:Kdo
ID: 24110841
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
 

Author Comment

by:nav29
ID: 24112022
Now
JP5DA03T is showing n :(
0
 
LVL 45

Expert Comment

by:Kdo
ID: 24114739

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
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 24117194
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
 
LVL 45

Expert Comment

by:Kdo
ID: 24117372
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
 
LVL 27

Accepted Solution

by:
tliotta earned 500 total points
ID: 24120542
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL0338 Error received on query 3 291
db2icrt -u db2inst1 db2inst1 problem on mac 4 242
DB2 Month between two dates 15 84
Another SQL with parm Part 2 44 71
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now