Solved

convert varchar to integer

Posted on 2009-04-09
18
860 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

11 Experts available now in Live!

Get 1:1 Help Now