[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

Substituting characters in Output

Hello Folks,

I have the following query i want to run

select cat_id, name, description, price, new from table_name
WHERE price > 0

Now the column "new" contains a bolean record which is either "Y" or "N" what i want to happen is that when the column returns results instead of outputting the "Y" I want the records to show the number "1" & "N" substituted by the number "0"

thanks in advance
0
Eaddy Barnes
Asked:
Eaddy Barnes
2 Solutions
 
ramkihardyCommented:
Option one....
you can creaye another table name supported table
and store the values
col1 col2
Y     1  
N      0.
and you put join from of two tables to get the desired values by writing query like this

select a.cat_id, a.name, a.description, a.price, b.col2 from table_name a join supportedtable b on a.new = b.col1 WHERE a.price > 0

Option 2...

select cat_id, name, description, price, new from table_name
WHERE price > 0
get the values from the query
and check for every by looping throught the rows and do as
if(new.toUpper() =="Y")
set new as 1;
else
new as 0;
Otherwise use Convert Statements in sql....that might help u i think...........
Regards
Ramki....
0
 
AmickCommented:
In MySQL it might look like this:

SELECT cat_id, 
name, 
description, 
price, 
CASE new WHEN 
    'Y' THEN 1 
 ELSE 0 
 END as NewProduct
FROM table_name 
WHERE price > 0

Open in new window

0
 
Eaddy BarnesITAuthor Commented:
I tried both and both worked , thanks guys..
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now