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  30421  @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 as an
empty string "". This is then updating the database with an empty
value rather than keeping...
|
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 std::string.c_str() but will
instead have to use std:;string.getData().
| | |
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 have tried String.Replace already but it is not working.
Please help
|
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> </Monitor>.
It's ok if the node value is blank, I just want to check for it and not throw an exception...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
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 captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |
