Replacing NULL Value In Multiple Columns In A Table
Dec 18, 2006
Hi,
I have some tables where I import data in, lots of field have gotten a
NULL value which the application can not handle.
Now can I replace each NULL value with '' in a columns with:
update <tableset [<column>] = '' where [<column>] IS NULL
But because there are lots of columns this is pretty much work, also
there are multiple tables.
Is there an easy way to replace all NULL values in all columns in a
table?
Thanks in Advance
Bob
View 2 Replies
ADVERTISEMENT
Jul 22, 2015
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3
**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
View 4 Replies
View Related
Jul 28, 2015
So I have a problem with combining cells: some of the cells are blank and some of the cells have NULL value.
The combined blank cells return perfect.
But the combined cells in which 1 or more contains NULL will return as NULL
0-4139-00-01OPTIONAL EQUIPMENT 139 - A -
0-4139-00-02NVG EQUIPMENT 139 - A -
0-4139-00-03NVG EQUIPMENT 139 - A -
0139-01-01BRAKE RESERVOIR NULL
A part of the script which i use is:
STUFF((SELECT ', ' + i.ac_type + ' - '
+ i.range_or_single+ ' ' + ' - ' + i.serialno_to
FROM msc_item_effectivity i
inner JOINmsc_item h
ONb.taskcard_verno_i = h.taskcard_verno_i
WHERE h.itemno_i = i.itemno_i
FOR XML PATH('')), 1, 1, '')
i.ac_type = 139
i.range_or_single = A
i.serialno_to = blank or NULL
I want the result of the blank value to be the same as the NULL value.
0139-01-01BRAKE RESERVOIR 139 - A -
View 2 Replies
View Related
Apr 29, 2015
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
View 14 Replies
View Related
Aug 10, 2015
Here is my requirement, How to handle using SSIS.
My flatfile will have multiple columns like :
ID key1 key2 key3 key 4
I have SP which accept 3 parameters ID, Key, Date
NOTE: Key is the coulm name from the Excel. So my sp call look like
sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date
View 7 Replies
View Related
Apr 15, 2014
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
View 4 Replies
View Related
Jul 23, 2007
Hello,
I'm using the query wizard in VB.net to write a query for SQL CE. I want to replace null values with text. I expected the COALESCE function to do this but I get an error message saying its not a valid function. This is a sample.
Select COALESCE(table.Name,'No Name') as Name from table
Any help appreciated
Thanks
View 7 Replies
View Related
Apr 13, 2007
Hi,
I am newbie to reporting services and I need some help. Could any one please let me know how to replace Null value with 0 in the data section of the Matrix. I don't want blanks to be displayed on the report, I want those to be replaced with 0's.
Thanks for your time.
View 4 Replies
View Related
Feb 12, 2015
I have an Parent table (Parentid, LastName, FirstName) and Kids table (Parentid, KidName, Age, Grade, Gender, KidTypeID) , each parent will have multiple kids, I need the result as below:
I need results for each parent like this
ParentID, LastName, FirstName, [Kid1Name,Kid2Name,Kid3Name], [Kid1Age,Kid2Age,Kid3Age],[kid1grade,Kid2grade,Kid3grade],[kid1gender,Kid2gender,Kid3gender]
View 1 Replies
View Related
Jan 11, 2005
Hello,
Is then any type of statement I could run to drop all the columns of a table that have a NULL value thoughout or must this be done manually?
Thanks,
Bryan
View 4 Replies
View Related
Feb 6, 2003
Hi,
Im having a couple of problems with SQL.
I have this query...
SELECT Band.Name, Member.Name
FROM Member
JOIN MemberOf ON Member.Mid = MemberOf.Mid
JOIN Band On MemberOf.Bid = Band.Bid
WHERE MemberOf.Instrument = 'keyboards';
which basically shows me the name of the bands who had keyboard players. I would like it to also display the names of the band who didnt have keyboard players, replacing the keyboard players name with "NULL".
so... my questions are :)
how do u get it to display the records that do not match the condition, and how do u get it to replace the keyboard players name with "NULL" when they do not match the condition.
PS. The three tables are
Band. Which has Bid as a primary key.
Member. Which has Mid as a primary key.
MemberOf. Which links these through its two foreign keys Mid and Bid.
Thanks for your help! (assuming someone does)
View 2 Replies
View Related
Jul 25, 2007
Here's one for the group:
I have a fixed-width flat file. Within some of the rows, I have embedded NULL characters. The inherit problem is that NULL characters are string terminators, so using a flat file source doesn't allow the capturing of these NULL characters or any characters after the first NULL character -- only the string up to the NULL character.
So, within SSIS, what would be the best way to replace NULL characters with a SPACE character? My file is fixed-width, and replacing with a space will allow me to keep the length the same. I am not opposed to running a script task against the file first (before using my flat file source), but would need some guidance as I'm not a .Net guru, by any means.
Unfortunately, going to the bank to have them correct this file has proved fruitless. We're going to have to deal with these characters on our side.
Thanks,
Phil
View 7 Replies
View Related
Oct 31, 2006
Hi,
I need help to build a query that shows me how many columns inside a range on columns are null.
Example: quantity1;quantity2;quantity3;quantity4;quantity5; quantity6;quantity7;
Which columns are null?
Thanks in advance
View 6 Replies
View Related
Jan 22, 2015
I have a table with 4 column in below
Total amount = 1000
salemancode1 = space
salemancode2 = Staff-99
salemancode3 = space
salemancode4 = staff-88
How I can write a one query statement to do this, we expect to count how many salemancode is not space and count the number of salesman to over the total amount.
total amount / (no_of_saleman) as commission
the result is 1000/ 2 the commission is $500.
View 1 Replies
View Related
Oct 20, 2005
Christine the Pharmacist writes "Please pardon as I am not horribly advanced with SQL...
Using SQL Server 2000, Windows 2000, Service pack 4
I'm trying to figure out marketshares for "preferred" products. I have a table put together by hospital and month of medication use with the totals of each drug dispensed in columns as well.
(example table below)
Hospital Month Drug A Drug B Drug C Drug D
A 7 5 10 58 73
B 7 NULL 26 98 43
etc.
** Drug C and Drug D are the preferred drugs
I'm getting correct preferred % in all cases except when a value is NULL (result is NULL). I'm using an aggregate (sum), which should ignore NULLs, but it ignores NULLs within a column, and not ignoring across columns. I've tried setting concat_null_yields_null off and I'm still getting NULL (since I'm using the plus sign).
This is my admittedly confused select statement, suspect this is where my problem lies (problems with saying "sum", but using "+" as well?):
select a.Hospital,a.month,convert(float,sum(c.Drug_C+d.Drug_D))/convert(float,sum(a.Drug_A+b.Drug_B+
c.Drug_C+d.Drug_D)) as preferred_share
Thanks for any advice!"
View 4 Replies
View Related
Nov 27, 2007
Hi all,
I am facing a problem on validating the data from a flat file while inserting the data into the destination table of sql server 2005 database. In my package, i have to validate the input data whether the values are coming as null or not, before inserting into the destination database. The flat file may not contain data for all NOT NULL columns. I have to find out that row(s) and reject the record. If the rows are coming as Null for the Not Null columns, the OLEDB Destination throws OLEDB exception for the constraint.
To resolve this, i have an script component in data flow, to check whether the input data is coming as null. I have added the output column of boolean type to the script component, it will be assigned to TRUE when there is null for the Not Null column in the script.
And in the follwing conditional split, i am checking the flag for TRUE to reject the record.
Is there any other way to handle this validation?
Regards
Madhav
View 6 Replies
View Related
Oct 29, 2013
I have a excel sheet with some data and blank columns. I have a ssis package using to import data from excel to sql table. For blank excel columns it is importing as null instead i want to show them as '0'. If data comes in it should update the data.
View 8 Replies
View Related
May 4, 2006
Hello,I have a query that I need help with.there are two tables...Product- ProductId- Property1- Property2- Property3PropertyType- PropertyTypeId- PropertyTypeThere many columns in (Product) that reverence 1 lookup table (PropertyType)In the table Product, the columns Property1, Property2, Property3 all contain a numerical value that references PropertyType.PropertyTypeIdHow do I select a Product so I get all rows from Product and also the PropertyType that corresponds to the Product.Property1, Product.Property2, and Product.Property3ProductId | Property1 | Property2 | Property3 | PropertyType1 | PropertyType2 | PropertyType3 PropertyType(1) = PropertyType for Property1PropertyType(2) = PropertyType for Property2PropertyType(3) = PropertyType for Property3I hope this makes sence.Thanks in advance.
View 3 Replies
View Related
Jul 9, 2007
I'm would like to replace all occurrences of "99999" and "-99999" with "" in a column using SSIS. I can use the REPLACE function in a Derived Column to replace one of those strings, for example: REPLACE(mycolumn,"99999",""). Or to replace both I could use REPLACE(REPLACE(mycolumn,"-99999",""),"99999",""). This seems kind of cumbersome and would get very complicated if I were replacing more strings with "". I'm guessing there is a better way. Can anyone help me out?
Thanks,
Ridium
View 12 Replies
View Related
May 21, 2008
I have records where there are multiple spaces between items of text. I would like to strip out all the excess spaces characters. To that end I have written to separate functions (both are listed below) neither of which I would imagine will be particularly efficient. The first uses an int table and the second a while loop. I am presently ignoring the leading and trailing space issue as this can easily be recitfied with LTRIM and RTRIM. However, I would be keen to know if there is a better way of achieving a similar result.
CREATE FUNCTION dbo.StripAdditionalSpaces (@Input varchar(1000))
RETURNS varchar(1000)
AS
BEGIN
DECLARE @Changed int, @Output varchar(1000)
SELECT @Output = ''
DECLARE @String TABLE ([Output] varchar(1000))
INSERT INTO @String ([Output]) VALUES (@Input)
SELECT @Output = @Output + SUBSTRING([Output], i1.Position, 1)
FROM @String
LEFT JOIN dbo.Ints i1
ON SUBSTRING([Output], i1.Position, 1) <> ' '
or ISNULL(SUBSTRING([Output], i1.Position - 1, 1), '') <> ' '
RETURN @Output
END
GO
CREATE FUNCTION dbo.StripAdditionalSpaces2 (@Input varchar(1000))
RETURNS varchar(1000)
AS
BEGIN
DECLARE @Output varchar(1000)
SELECT @Output = @Input
WHILE @Output LIKE '% %'
BEGIN
SELECT @Output = REPLACE(@Output, ' ' , ' ')
END
RETURN @Output
END
GO
View 12 Replies
View Related
Nov 25, 2014
I have two tables table1 and table2. I want to check a value from table1 against 4 different columns in table 2. What would be the most optimized way to do this. I came up with this SQL but it runs forever.
select * from table1 a
where
(a.id in (select orig_id from table2 where exctn_dt >= '01-OCT-14')) or
(a.acct_intrl_id in (select benef_id from table2 where exctn_dt >= '01-OCT-14')) or
(a.acct_intrl_id in (select send_id from table2 where exctn_dt >= '01-OCT-14')) or
(a.acct_intrl_id in (select rcv_id from table2 where exctn_dt >= '01-OCT-14'));
View 5 Replies
View Related
Jan 3, 2007
How do I alter multiple columns with one SQL statement?
I've tried :
ALTER TABLE epcs_benefit_plan ALTER COLUMN
abc1 varchar(3) not null,
abc2 varchar(3) not null
View 3 Replies
View Related
Nov 11, 2005
Just wondering. On some workstations and a couple of servers we
have apps each running an instance of MSDE so these machines each have
anywhere between 2 - 5 instances of MSDE each.
Would it be benificial from a resources point of view (not to mention a
patching one) if we set up one instance of Express to replace the
multiple MSDE ones?
Anyone got any thoughts?
Antony
View 1 Replies
View Related
Apr 24, 2014
-- Here's a test table where I'm trying to workout how to Pivot more than one column.
-- Drop the Temp Table
IF (SELECT Object_id('tempdb..#Test_Pivot_Example')) <> 0
BEGIN
DROP TABLE #Test_Pivot_Example
END
[Code] ....
Once I have worked this out then I need to dynamic populate the IN ([1] etc with the val;ue sin field [SIZE])
but one step at a time trying to workout pivot on more than one column.
View 4 Replies
View Related
Nov 15, 2013
I want to update multiple column in one table using with case statement. i need query pls..
stdidnamesubject result marks
1 arun chemistry pass 55
2 alias maths pass 70
3 babau history pass 55
4 basha hindi NULL NULL
5 hussain hindi NULL nULL
6 chandru chemistry NULLNULL
7 mani hindi NULLNULL
8 rajesh history NULLNULL
9 rama chemistry NULLNULL
10 laxman maths NULLNULL
View 2 Replies
View Related
Jan 8, 2007
Hi everyone. I am updating a table with aggregate results for multiplecolumns. Below is an example of how I approached this. It works finebut is pretty slow. Anyone have an idea how to increase performance.Thanks for any help.UPDATE #MyTableSET HireDate=(Select Min(Case When Code = 'OHDATE' then DateChangedelse null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),TerminationDate=(select Max(Case When Type = 'N' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),ReHireDate=(select MAX(Case When Code = 'HIRE' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and #MyTable.HRRef=HREH.HRRef )
View 2 Replies
View Related
Oct 12, 2015
I have a Problem with my SQL Statement.I try to insert different Columns from different Tables into one new Table. Unfortunately my Statement doesn't do this.
If object_ID(N'Bezeichnungen') is not NULL
Drop table Bezeichnungen;
GO
create table Bezeichnungen
(
Artikelnummer nvarchar(18),
Artikelbezeichnung nvarchar(80),
Artikelgruppe nvarchar(13),
[code]...
View 19 Replies
View Related
Aug 14, 2012
I have a table with a string value, where all values are seperated by a space/blank. I now want to use SQL to split all the values and insert them into a different table, which then later will result in deleting the old table, as soon as I got all values out from it.
Old Table:
Code:
ID, StringValue
New Table:
Code:
ID, Value1, Value2
Do note: Value1 is INT, Value2 is of nvarchar, hence Value2 can contain spaces... I just need to split on the FIRST space, then convert index[0] to int, and store index[1] as it is.
I can split on all spaces and just Select them all and add them like so: SELECT t.val1 + ' ' + t.val2... If I cant find the first space that is... I mean, first 2-10 characters in the string can be integer, but does not have to be.Shall probably do it in code instead of SQL?Now I want to run a query that selects the StringValue from OldTable, splits the string by ' ' (a blank) and then inserts them into New Table.
Code:
SELECT CASE CHARINDEX(' ', OldTable.stringvalue, 1)
WHEN 0 THEN OldTable.stringvalue
ELSE SUBSTRING(OldTable.stringvalue, 1, CHARINDEX(' ', OldTable.stringvalue, 1) - 1)
END
AS FirstWord
FROM OldTable
Found an example using strange things like CHARINDEX..But issue still remains, because the first word is of integer, or it does not have to be...If it isn't, there is not "first value", and the whole string shall be passed into "value2".How to detect if the very first character is of integer type?
Code:
@declare firstDigit int
IF ISNUMERIC(SUBSTRING(@postal,2,1) AS int) = 1
set @firstDigit = CAST(SUBSTRING(@postal,2,1) AS int)
ELSE
set @firstDigit = -1
[code]....
View 2 Replies
View Related
Jan 23, 2014
I am trying to pivot table DYNAMICALLY but couldn't get the desired result .
Here is the code to create a table
create table Report
(
deck char(3),
Jib_in float,
rev int,
rev_insight int,
jib_out float,
[Code] .....
Code written so far. this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(deck)
FROM (SELECT p.deck FROM dbo.report AS p
GROUP BY p.deck) AS x;
[Code] ....
I need all the columns to be pivoted and show on the pivoted table. I am very new at dynamic pivot. I tried so many ways to add other columns but no avail!!
View 1 Replies
View Related
Jan 13, 2015
I have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.
I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:
DatabaseName DatabaseVersion DateChangedOn
OK5_AAGLASS 5.10.1.2 2015/01/12
OK5_SHOPRITE 5.9.1.6 2015/01/10
OK5_SALDANHA 5.10.1.2 2014/12/23
The results should be ordered by DateChangedOn.
View 4 Replies
View Related
Jun 23, 2015
I've a database with a table that has 16 columns that are searchable. There can be a numerous combination of those columns used for searching...
In this case the best solution is to create an index on each column individually or at least the most used?
View 7 Replies
View Related
Mar 1, 2008
Sorry for the confusing subject. Here's what im doing:I have a table of products. Products have N categories andsubcategories. Right now its 4. But there could be more down theline so it needs to be extensible.So ive created a product table. Then a category table that has manycategories of products, of which a product can belong to N number ofthese categories. Finally a ProductCategory "match" table.This is pretty straigth forward. But im getting confused as to how towrite views/sprocs to pull out rows of products that list all theproducts categories as columns in a single query view.For example:lets say productId 1 is Cap'n Crunch cereal. It is in 3 categories:Cereal, Food for Kids, Crunchy food, and Boxed.So we have:Product----------------1 Capn CrunchCategories-----------------1 Cereal2 Food for Kids3 Crunchy food4 BoxedProductCategories------------------1 11 21 31 4How do I go about writing a query that returns a single result set fora view or data set (for use in a GridView control) where I would havethe following result:Product results---------------------------------ProductId ProductName Category 1 Category 2Category 3 Category N ...------------------------------------------------------------------------1 Capn Crunch Cereal Food for Kids Crunchy foodBoxedAm I just thinking about this all wrong? Sure seems like it.Cheers,Will
View 1 Replies
View Related
Dec 13, 2007
hai, iam new to ssrs please help me,
my report having 10 columns, i can explain my need with example that is, assume like this customer id is first column of my table customer id is 101 it having 3 departments a1,b1,c1,perticular department that is a1 having emp1,b1 having emp2,c1 having emp 3.
i want output like this when clicking + customer id driildwon it display 3 departments taht is a1,b1,c1, when +a1 drill down clicking i need to dispaly emp1, corresponding b1 to emp2 , c1 to emp 3.
above explanation is only one column of the table, like that iam also displaying this driiling procedure for remaining different columns.
and i need to display customer information by weekly,daily,monthly,yearly at bottom of the report
please give which logic used in creating format like above drilldown report which having multiple drilldowns for all columns in a table
if any body give procedure for creating fromat for drilldown report which having multiple drilldowns for all columns in a table is appriciate.
thanks
jacks
View 1 Replies
View Related
