I am trying to create a query such that when pulling data from a table, then
If the result is "NULL", replace "NULL" in the column with a "blank"...O R... if the result is a string (not NULL), strip all commas from the resulting string and show the result in the column (Example: showing "ABC, Inc." as "ABC Inc.")...AND... label the column with a heading.
So far, I have totally failed in attempts to write this line of my query...
Many Thanks!
Frustrated Newbie
7 30406 @cbjones
Look at case statement useage typically - SELECT YourColumnName,CASE WHEN ColumnName Is Null THEN 'blank' ELSE replace(YourColumnName,',','') END AS MyColumn from dbo.YourTableName
I know you did not want specifically the word 'blank' but you get the idea just remove and leave single quotes instead
I know some query designers GUI interfaces dont support case statement useage Access ADP project files for one, but then you just create the view on the server and call the view. Case statement is flexible as you can see you can add many arguments to it.
Many thanks...here's what I did:
SELECT MailAddress2,CA SE WHEN MailAddress2 Is Null THEN '' ELSE replace(MailAdd ress2,',',' ') END AS [Insured Address 2] from Insured
and I got two columns. What am I doing wrong?
MailAddress2 Insured Address 2
Suite C10 Suite C10
NULL
NULL
NULL
NULL
NULL
NULL
NULL
1020 Hidden Harbour Dr. C-1 1020 Hidden Harbour Dr. C-1
NULL
NULL
@cbjones
You are doing nothing wrong. You asked for two columns of data in your SELECT portion one being the data column and the other being the derived from a calculation Case statement. If you dont wantt the first column just remove it - SELECT CASE WHEN MailAddress2 Is Null THEN '' ELSE replace(MailAddress2,',',' ') END AS [Insured Address 2] from Insured
@Jim Doherty
Manually remove it? I can do that if need be. Thanks Jim.
Just manually change the SQL to match my last post. Your MailAddress2 data will remain in your table as it should do of course. The SQL Case statement is merely testing that data and returning a calculated column ONLY if you understand me
@Jim Doherty
EUREKA! Thanks for the ideas! Here is the winner in the clubhouse; it first determines if the content is NULL and, if so, suppresses displaying NULL, and if not, removes the commas from the existing string, if any, and replaces the commas with a "-" {dash}:
(CASE WHEN I.MailAddress2 is NULL THEN IsNull(I.MailAd dress2, '') ELSE REPLACE(I.MailA ddress2, ',','-') END) as [Insured Address 2]
Thank you thank you thank you!
@cbjones
Hahahah so pleased you conquered it. Well done! :)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Tilmann |
last post by:
I'm currently using UDB 7.2 on WIN 2000 and also DB2 Version 7 on
z/OS.
I have one table with a column defined as NOT NULL. I had to change
this column from NOT NULL to NULL. Is there any better way than
UNLOAD, DROP TABLE, CREATE TABLE and LOAD? I didn't find anything in
the alter table statement in this direction?
|
by: Dana |
last post by:
I am using the XMLTextWriter to build an XML string and pass it to the
XMLDocument. When I get the data from SQL Server, some of the values
passed to the XML are NULL in the database. When I try and run an
update to database using the same XML string, (using SQL parameters to
pass the selectsinglenode), the XML always shows the NULL values...
|
by: Mark Kamoski |
last post by:
Hi--
What is the difference between Convert.ToString(obj) and CType(obj,
String)?
(Assume obj is a variable of type Object.)
Please advise.
Thank you.
|
by: young_leaf |
last post by:
Using STL how do I convert a variable to a binary string but with
special case which
for example BYTE x, i need only the first 3 bits of this byte, is that
possible?
to be more specific,
from that byte i want to generate (so valid values for x will be 0-5)
000
001
|
by: Pep |
last post by:
I have to interface to an older library that uses strings and there is
no alternative. I need to pass a string that is padded with null
bytes. So how can I append these null bytes to the std::string?
Yes I know it would be better to use something like a vector but I do
not have that option.
Yes I know that I will not be able to use...
| |
by: Manuel |
last post by:
Hi, I need to convert a text file in a string or char*. exists some
function that does it? or how can i do it?
Thanks,
bye
|
by: satishrajana |
last post by:
Hi,
My SQL returns a NULL in a datefield if there is no date in that field.
If there is a NULL in this column, I want to replace it with spaces in
my SELECT statement when I am selecting these records. Can any of you
experts out there help me with a sample SQL.
|
by: santoshsri |
last post by:
Hello all,
I am entangled in a difficult situation. I wish to remove commas in a string ( for example : 1,20,000 ) that is coming as an output from another system.
I will have to process the output in my C# application. I will have to save 1,20,000 as 120000 in SQL2K database.
Please let me know how to remove the commas from the string . I...
|
by: jrlittle86 |
last post by:
I know I'm overlooking something very simple but How can I check if the text within an XML tag is blank?
The code below works fine as long as there are values within an XML node (For example the <CPU>Intel</CPU> node below) but it throws an Exception error when the node value is blank/null whatever as in the example node <Monitor> ...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |