Thank u Chirag....
but i should display all the records of that table not only price column.
consider the column price in titles table in pubs database. If the price of any record is not defined it should be retrieved as -9999.
I need to separate the data from one column in to two columns based on the transaction type TRAN_TYPE (C is credit, D is Debit)
TRAN-TYPE| AMOUNT
C 20.00
D 30.00
C 50.00
To do that I have this code:
select
CASE WHEN TRAN_TYPE = 'D'THEN CAST (ISNULL(amount, 0) as varchar (30)) end as DEBIT,
CASE WHEN TRAN_TYPE = 'c'THEN CAST (ISNULL(amount, 0) as varchar (30)) end as CREDIT
FROM HISTORY
And my output is:
DEBIT | CREDIT
----------------
NULL | 20.00
------|-------
30.00 | NULL
------|-------
NULL | 50.00
------|-------
how to replace the null values with 0
select distinct
case when item is null then replace(item,null,'-')
else itemend
end as item
from itemstable
i want to replace all null values to '-'...but still i m getting null values..
syntax is correct still not getting results..
can anyone help?
thanks.
I need to convert a a string column to integer. Before converting, I need to check if it has blank values then convert it to NULL. Someone told me that its easier to convert it to NULL before converting to integer.
View 5 Replies View RelatedI have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
-- Ryan
I have a table that lists math Calculations with "User Friendly Names" that look like the following:
([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])
I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)
The new formulas need to look like the following:
([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])
I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.
How can I accomplish this?
Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.
DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )
[Code] .....
how can I insert a constant value such as the word "NoPhone" when a field value from a select statement is null?
Here is my code:
select AgencyName
,AgencyLoc,
,ISNULL(AgencyPhone.Agency,) as phone
How can we put in the words "nophone" when phone is null?
Hi all smart people,
Can someone please help me with how to replace null values with spaces in select statement.
I have to have a select statement which will select all the data but the field contain null value it has to replace with spaces. How can I do that ?
Thanks,
Can a Trigger be created to automatically replace NULL values with "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to code in my application. I want that when an Insert Query is executed, a BEFORE/AFTER TRIGGER should be executed to replace the NULL values coming from Insert QUery to "-".
I also want to know whether to use a Trigger or a Function/Stored Procedure.
I have a colunm name (Countystcd) in a matrix, and i have used the mentioned colunm in Data Region of Matrix which shows thw sum of count for each state, i want to replace null with 0 in report please help me in this regard.
thanks
regards
Mohammad Yaseen
Hello,I'm a beginner in SQL and I have been searching through the SQL Cookbook and Google but I can't seem to find an example of what I want to do. I want to create a report that will return names and emails using two of my tables. I want to use the email in my primary table in the select but if it is null or empty I want to replace it with an email from my secondary table. Below is what I would like to do but I got a syntax error with it in SQL Server 2000. SELECT MemberID As ID, MemberFirstName As FirstName, MemberLastName As LastName, (IF MemberEmail = '' THEN SELECT TOP 1 OtherEmail FROM OtherTable WHERE OtherID = MemberID) As EmailFROM PrimaryTableThanks for your time.Jason
View 8 Replies View RelatedHi
I'm trying to create a stored procedure using the northwind db which will do the following:
SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock
FROM Products
However, where UnitsInStocks = 0 I would like the words "Sorry, out of stock" to appear. I will then call this from an ASP page.
Can anyone help please?
Cheers
Woolly
How to replace DateTime field with null value in SQL 2005 server
I create a stored procedure aa, It works well, but sometimes I hope to replace CreateDate field with null value,I don't know how to doIt seems that datetime type is not null value
create aa @CreateDate DatetimeAsUpdate cw set CreateDate=@CreateDate
Dear Community,We have a problem with null-Bytes in varchar-Columns, which are nothandled correctly in our application. Therefor we try to filter themout using the Transact-SQL REPLACE function.The Problem was, that the REPLACE-function didn't behave the way weexpected.Following Example demonstrates the behavior:declare @txt varchar(512)declare @i intset @txt = 'hello ' + char(0) + 'world'print @txtset @i = 1while @i <= len(@txt)beginprint str(@i) + substring(@txt, @i, 1)set @i = @i + 1endprint 'Length: ' + str(len(@txt))print 'trying to replace null-byte:'print replace(@txt, char(0), '*')print 'replace Letter h'print replace(@txt, 'h', char(39))-- end exampleOutput:hello1h2e3l4l5o678w9o10r11l12dLength: 12trying to replace null-byte:*replace Letter h'elloThe Null-Byte replace destroys the whole string. This behavior occursonly on some of ourdatabases. The others work correctly.Is it possible that it depends on some server setting?ThanksEnno
View 5 Replies View RelatedI have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
View 12 Replies View RelatedHi.
1.
Have a query that fetches a real value (e.g. 4.3345643)
Let say the field is called TheReal.
How can I format the value in the select statement so I get a thousand separator(s) and two decimals in the resultset.
2. In the same query I have a left join as well.
Table 2 retur (sometimes) <NULL>.
Is it possible to force this <NULL> value to be a fixed string value instead in the select statement.
Ok.. so I have a fixed position data feed. I read the file in as just whole rows initially, process a specific position and evaluate a conditional split to determine direction of the file for proper processing (file contains multiple recors with different layouts). This all works fine. I then use the derived column feature to process all the columns.
Most of the columns are as simple as SUBSTRING(RecordData,1,10) for example to get the first column. This all works for string data. I then have a column that is a date field. The problem occurs that the code SUBSTRING(RecordData,20,10) returns either a date or empty set of data if no date was in the original file. When this gets sent to the OLEDB connection (SQL Server 2005) into the date field it fails. If the record has a date it works, but if it is empty it fails the insert.
I tried to replace empty strings with NULLs with this code. REPLACE(TRIM(SUBSTRING(RecordData,20,10)),"",NULL(DT_WSTR,10)). This does not work. So my question is how do I bring a date field from a fixed flat file into a SQL datetime field using a derived column? More specifically is how do I set it to NULL if its empty data? When I use the above code it inserts all the rows from the file, but it sets all rows to NULL not just the empty ones.
Thanks.
I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales](
[CustomerId] [nvarchar](50) NULL,
[CustomeName] [nvarchar](50) NULL,
[CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]
Hi
can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task.
In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful.
Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.
If Not Row.Datum1_IsNull Then
Row.OutputDatum1 = Row.Datum1
Else
Row.OutputDatum1 = CDate(System.Convert.DBNull)
End If
Any help welcome.
'000000' is a string with 6 charaters
Requirement:
I want to Replace 0 with 1.
Replace 0 to 1 at position 3 output= '001000'
Replace 0 to 1 at position 5 output= '000010'
hi,
In the result of a function in my query, there are negative numbers.
How do I replace them with a 0 or is there a function like ISNULL that replaces the values that are negative?
thanks,
maarten
I was wondering how to do a find and replace with SQL? Would I use a SET statement?
I need to find a specific value and replace all of the results with a different value.
Thanks for all responses.
HI,
I AM HAVING A TABLE WHICH HAS INCREMENTAL COLUMNS,WHERE COLUMNS GETS ADDED EVERY MONTH TO THE TABLE AND THE TABLE THEN CONTAINS PREVIOUS MONTH AND PRESENT MONTH DATA ABOUT CUSTOMERS ,DETAILS AND TRANSACTIONS.
THE PROBLEM WITH THIS DATA IS ,IF THE CUSTOMER IS NEW ,THEN IN PREVIOUS MONTHS HIS INFORMATION IS NULL,WHICH HAVE TO BE CODED HAS "NOT PRESENT".
NOW,
HOW DO WE CONVERT ALL THE PREVIOUS COLUMNS FOR A PARTICULAR CUSTOMER HAS NULL AT THE SAME TIME ?.
HERE IS HOW THE PROC WRITTENED FOR IT GOES :-
DROP PROCEDURE DE_NAT
CREATE PROCEDURE DE_NAT
AS
BEGIN
DECLARE @MONMIN1 NVARCHAR(100),MON NVARCHAR(100),@YEAR NVARCHAR(100) , @MONYEAR NVARCHAR(100)
SET @MONMIN1 = DATENAME((MONTH),DATEADD(MONTH,-1,GETDATE()))
SET @MON = MONTH(GETDATE())
SET @YEAR = YEAR(GETDATE())
SET @MONYEAR = @MON + @YEAR
EXEC('select A.CUSTOMERS,B.*,CAST(A.RFM_40D AS FLOAT) AS R40
INTO TSD_' + @MONYEAR
+ ' from TSD_20 A
LEFT OUTER JOIN SD20 ' + @MONMIN1 + ' B
ON A.CUSTOMERS = B.CUSTOMER')
END
THIS PROC JUST ADDS THE PRESENT MONTHS DATA TILL LAST MONTHS DATA.
BUT IF A CUSTOMER IS NEW, THEN HOW DO I REPLACE THE NULL VALUES FOR THE PREVIOUS DATA TO 'NOT PRESENT'
FOR EG :- IF THERE IS A NEW CUSTOMER ,HOW DO WE CHANGE :-
CUSTOMERS ERTYYTRE RTYUUYTR TYUIIUYT QWERREWQ DFGHHGFD
----------- ---------- ---------- ---------- ----------- ----------
101023 <NULL> <NULL> <NULL> <NULL> 1.0
102022 1.0 1.62.3 3.4 4.5
NOW, AS YOU CAN SEE, THAT FOR CUSTOMERS = '101023'.
THE COLUMN DFGHHGFD IS, THIS MONTHS DATA , I WANT TO CHANGE ALL NULL VALUES PRESIDING IT AS "INACTIVE"
CAN I CHANGE , ALL COLUMNS FROM NULL TO "INACTIVE" , AT THE SAME TIME. ?
AS NEXT MONTH, AGAIN THE COLUMNS IS GONNA INCREASE WHICH WILL
AGAIN CAUSE A PROBLEM .
PLS TELL ME A METHOD , SO THAT I CAN DO THE NEEDFUL.
Hi,
My query "select blah, blah, rank from tablewithscores" will return results that can legitimately hold nulls in the rank column. I want to order on the rank column, but those nulls should appear at the bottom of the list
e.g.
Rank Blah Blah
1 - -
2 - -
3 - -
NULL - -
NULL - -
At present the NULLs are at the top of the list, but I do not want my ranking in descending order. Any suggestions?
Thanks
Dan
I need one query:
create table #task(TaskId bigint unique, Name varchar(2000))
insert into #task values(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
insert into #task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>> Text Text Text <<Salary>> Text Text Text')
[Code] ....
Now I need to create an inline function who resolve the task name with appropriate values and return me the resolved task name
select * from fn_TaskResolver(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
I try this function but its return multiple rows as i just want to return one row. as I have big data set so i don't want to use scaler or Multi Line function.
create function fn_TaskResolver(@TaskId bigint, @name varchar(2000)
Return table
as
return
[Code] ....
The string column value looks like as below. Each value has a size of 15 withing a string
'2.2020 30 4.0000'
The column value should match with user input as below. The result should show equal when it is compared. Currently, it results not equal since it is a string comparision. The last digit '0' needs to be ignored for decimal values.
'2.202 30 4.0'
I need to handle the decimal values in such a way, if staring value with '.' and last digit is 0 then replace with space ''. So, it should look like
'2 2 2 30 4 ' = '2 2 2 30 4 '
When this string is compared, it results in EQUAL.
I tried the below logic, which even replaces the integer value like 30 to 3 and 3000 to 3 and results in equal which is incorrect.
RTRIM(REPLACE(REPLACE(RT1.rate,'''+@DOT+''','''+@S PACE+'''), '''+@ZERO+''', '''+@SPACE+''')) = '''+REPLACE(REPLACE(@Rate,'.',' '), '0', ' ')+''' '
Ex:'2.2020 300 4.00' = '2.20200 30 4.0'
After replace, string looks like
Ex:'2 2 2 3 4 ' = '2 2 2 3 4 '
It results as EQUAL which is incorrect. I need only decimal value to be replaced not integer.
I am looking for a single string replace logic.
I need one query...
create table #task(TaskId bigint unique, Name varchar(2000))
insert into #task values(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
insert into #task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>> Text Text Text <<Salary>> Text Text Text')
-- select * from #task
[Code] ....
Now I need to create an inline function who resolve the task name with appropriate values and return me the resolved task name
select * from fn_TaskResolver(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
I try this function but its return multiple rows as i just want to return one row. as I have big data set so i don't want to use scaler or Multi Line function.
create function fn_TaskResolver(@TaskId bigint, @name varchar(2000)
Return table
as
return
(
with data as
[Code] ....
Running into a problem with trying to pull multiple values in a stored procedure.
Ran Profiler for trace on what variable is coming back as when running in Reporting Services.
Profiler shows this:
exec apGetCompanysByRep @Product = N'Absorbent Pads,Airedale Terrier'
Trying to run Replace function but not quite getting the syntaxt correct. The stored procedure will work but when running report will often get unclosed quotation marks.
What I have:
Set @Product=
Replace(@Product, '''' + ',' + @product + '''', (@Product + '''' + ',' + ' ' + '''' + @product + ''''))
This is probably a little too much coding but I've tried several ways.
In the SP the code is as follows:
CREATE PROCEDURE [dbo].[apGetCompanysByRep]
@Magazine varchar(6) = null
, @DirectsMagazine varchar(6) = null
, @Category varchar(50) = null
, @SubCategory varchar(50) = null
--, @FirstName varchar(30)
, @LastName varchar(30)= null
, @Product varchar(1100)
AS
declare @SQL varchar(2000)
Set @Product=Replace(@Product, '''' + ',' + @product + '''', (@Product + '''' + ',' + ' ' + '''' + @product + ''''))
set @SQL = '
Thanks for any assistance anyone can give.
There is a table [Formula_Calc] with formula calculations that need to be replaced with relevant values based on another table [Totals]
[Totals]
RowNo|Total
F1|240
F2|160
F3|180
F11|1000
F12|1500
F13|2000
For example we've got a row from [Formula_Calc] table 'F1+F3' as a string that needs to be transformed as 240+160=400
The below code works for the above example but if I pick 'F11+F3' instead , returns 2561 which comes from 2401+16.
Probably replaces F1 value instead of F11 and adds 1st digit (1) if I got it right ...
DECLARE @formula NVARCHAR(100);
DECLARE @Total NVARCHAR(100);
SET @formula = 'F11+F3';
SELECT @formula = REPLACE(@formula,RowNo,Total)
FROM [Totals]
SET @Total='select '+@formula
EXECUTE sp_executesql @Total;
PRINT @Total;
I am having an issue when trying to do something that looks simple on the face of it.. I want to replace the value during update in the same table.
Here's my situation
tst_update
ID int
Col1 varchar(10
I want to ensure that everytime Col1 is updated to "A" I want to set it to "X". Otherwise leave it as is.
Can someone help.
Thanks,
Ashish
how we can replace the multiple values in a single select statement? I have to build the output based on values stored in a table. Please see below the sample input and expected output.
DECLARE @V1 NVARCHAR(100)
SELECT @V1 = 'FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 '
DECLARE @T1 TABLE
(FN VARCHAR(100), LN VARCHAR(100), A1 VARCHAR(100), A2 VARCHAR(100))
[code]....
I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.
Hi, I ve a table, I want to fetch certain rows based on the value of a Column. That column is nullable, and contains NULL values.I used the following query,SELECT Col_A FROM TABLE1 WHERE SOME_ID = 1317 AND Col_B NOT IN (8,9) Here, Col_B contains NULL values too. I need to fetch all rows where Col_B is not 8 or 9.Now, if I use "NOT IN", it does not work. I tried reading on it and got to know why it does not work. Even "NOT EXISTS" does not help. But still I've to fetch my values. How do I do that?Thanks & Regards,Jahanzeb
View 6 Replies View Related