Derived Column Logic To Replace Empty String With Null?

Nov 7, 2006

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.

View 6 Replies


ADVERTISEMENT

Single String Replace Logic - 0 Needs To Be Ignored For Decimal Values

May 10, 2012

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.

View 3 Replies View Related

Within A SELECT, How Do I Replace An Empty Or Null Value With A Value From Another Table?

Apr 20, 2008

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 Related

How To Set Empty A Space From A Derived Column Expression?

May 30, 2006

hi,

This field comes from a flat file and sometimes own zero and sometimes own a empty position (after 'EUR'):

2006053000499236000005307700108287457080200408287452006052953990000000010000EUR
2006053000499236004414989200101423426004400501423422006052953990000000010000EUR0

[Column 12] == "0" ? [Column 12] : ?????????



TIA

View 7 Replies View Related

Derived Column Task Replace Quotes

May 14, 2007

How do you replace quotes in an expression



I mean for example if I needed to replace xx in a string with empty string then the following works: REPLACE(SelectedString, "xx","")

But the example I have needs to actually replace quote marks in a string with an empty string and REPLACE(SelectedString, " " ","") doesn't work. I tried guessing a few option like &QUOTE or &QTE or something...

Any ideas ?



Thanks

Richard

View 1 Replies View Related

Sample Problem : Function Replace() In Derived Column

Mar 22, 2007

Hello All.

Hopefully someone out there will have an idea as this isdriving me nuts.
Ihave some sample problem. I want to use function replace() on Derived Column.
For example.
when strDate = 2007/03/22

I used ==> replace(strDate, "/", "") ==> 20060322

But If strTest = "123.10" ====>> 123.10

How can i do to replace ( " )double qoute ?
by function replace()

what is a statement for replace (") in Derived Column ?

please tell me for this event.

any suggesstion appreciated
Thank you very much.

Chonnathan

View 11 Replies View Related

Major Problem With REPLACE-expression In The Derived Column Transformation

Apr 26, 2006

I'm importing a csv-file delimited with semicolons. Firstly I LTRIM the columns "in place" and the data imports fine. All the numbers in right columns in the target table. Then I add another Derived Colum Transformation to replace decimal character comma (,) to a dot (.) in order to convert the string/varchar value to numeric. But here I run into trouble. Running the task ends in success but the result in the target table (same as above) is not. All the commas are now dots as expected but what is worse is that SSIS have added values in cells that should not be there. I get values in cells that shoud be empty!

Shortly: Only LTRIM([Column1]) as expression and "Derived Column" as Replace 'Column1' works OK.

But adding REPLACE-expression (i.e REPLACE(LTRIM([Column1]) , "," , ".") to this breaks things up

I'm aware that I could do this with SQL but this is not the point...

Any ideas?

BR Jompe

View 6 Replies View Related

Empty String To Null

Jul 11, 2001

example
create view v_GuestOrder
as
Select T1.id_Guest,T2.OrderName
from Guest T1
left join Order T2 T2.id_order = T1.Id_order


select * from v_GuestOrder
--
Id_Guest OrderName
-------- -----
1 spoon
2 phone
3
4 tv

I need something similar to

Select
id_Guest,
case orderName
when '' then Null -- Sql server gives error in thsi case
end as orderName
from v_GuestOrder

So I need to assign NULL to OrderName is query return empty string,
it will be treated by Crystal reports as Null

Please help , thanks

View 3 Replies View Related

Checking If String Is NULL Or EMPTY In SQL

Nov 9, 2007

I need to check in my Stored procedure if the information passed is null or empty so I can decided to insert the new value or keep the old. How do I accomplish this please in T-SQL. Thanks in advance.

View 6 Replies View Related

Insert Empty String Or Null To Sql Db

Jan 12, 2008

Hi:
Trying to insert null value into sql table, but not working, if I use:
 if (strMyText.Length == 0)        command.Parameters.Add("@Text", DBNull.Value); // or using:("@Text", null), or using:("@Text", DBNull) else         command.Parameters.AddWithValue("@Text", strMyText); 
When I go back to table, I see the value is: 'NULL', has single quotation mark, suppose to be: NULL
Where is the problem?
Thanks a lot.
Jt

View 3 Replies View Related

Null Or Empty String Use More Space?

Aug 9, 2005

Hi all,   I have some columns in my database which allows null.  I want to know if leaving the field to be NULL or storing an empty string into the field, which will take up more space?? if the field type is varchar(100)

View 5 Replies View Related

HELP : Empty String To Null Conversion By SQL

Nov 10, 2005

Hi folks,

I've have about 100 tables, for some reasons, column values that are originally NULL was inserted as emtpy string. So, I am wondering if I can write JUST ONE SQL (hopefully don't have to specify the field names in the SQL as well) for each table so that all the empty strings will be converted back to NULL.

THANKS JOHN

View 3 Replies View Related

REPLACE In Derived Column Transform Causing Repeat Data In Rows That Should Be Blank

Jul 25, 2006

W2k3 server, SQL 2005.
@@version = Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Standard Edition on Windows NT 5.2
(Build 3790: Service Pack 1)

I have my first SSIS package almost working, but I'm having an odd problem and can't find any information to help resolve it.

I'm importing from a flat file (csv) to an existing table (append). I've got a Derived Column transformation in the middle to do some data cleanup. It's all working except for one little problem...

One of the transformations is 'REPLACE([Column 3],"^","; ")', output to a new column. (The input file has a field that uses carets as delimiters between an unknown number of items; I'm changing that to semicolons for easier reading.) Not all rows have data in this column, some will have one item, some will have multiple items.

The REPLACE works except that it fills in repeated data for all the blank rows.

Example:

Incoming data is:

1 Smith,Jane^Jones,Jane

2 Brown,John

3

4 Adams,James^Adams,Jim

5

6 White,Debra

Data inserted into the table is:

1 Smith,Jane; Jones,Jane

2 Brown,John

3 Brown,John

4 Adams,James; Adams,Jim

5 Adams,James; Adams,Jim

6 White,Debra

I've tried to use a Conditional to skip the empty rows, but I can't get that working at all (get syntax errors no matter what I put in).

Any suggestions on how to fix this would be most appreciated!

Thank you.

View 5 Replies View Related

NULL Values Vs Empty String Vs Space

Oct 25, 2006

How do I define a field to have the default value = ''. Not NULL but not a space either in SQL Server 2005?

View 10 Replies View Related

Newbie Search/replace Case Style Functionality In Derived Column Expression Syntax

Feb 6, 2008



Hi guys,

I am looking for some syntax to help me with a traditional search/replace style requirement. I am wanting to examine the contents of one column and populate another.

similar to this SQL case statement

CASE ProductLine
WHEN 'R1' THEN 'Road'
WHEN 'M1' THEN 'Mountain'
WHEN 'T1' THEN 'Touring'
WHEN 'S2' THEN 'Other sale items'
ELSE 'Not for sale'
END,


the twist is that R1, M1 etc. can appear anywhere in the ProductLine column.

thanks for any assistance you can provide.

regards,

Chris

View 1 Replies View Related

Detect And Convert An Empty String (from Textbox) To Null?

Jun 9, 2007

 Hi all,I have this code that I use for my Search function:SELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER]FROM t_musicWHERE (@MUSIC_TITLE IS NULL OR [MUSIC_TITLE] LIKE '%' + @MUSIC_TITLE + '%') AND (@MUSIC_ARTIST IS NULL OR ([MUSIC_ORIGINAL_SINGER] LIKE '%' + @MUSIC_ARTIST + '%' OR [MUSIC_PERFORMER] LIKE '%' + @MUSIC_ARTIST + '%')) But right now if I don't enter anything in one of the textbox (2 have two, either of them can be left empty), the above Sql statement doesn't return anything since ADO.NET can't tell an empty textbox and treat it at null... So anyone please help me how to detect an empty textbox and set that to null for the above SQL statement to work. (It work in SQL Manager Studio, when I set one of the parameter = null.) I'm very new to ASP.NET stuffs, so if someone can help me to convert that function to code-behind and help me to call it from the .aspx, that would be even better as I don't want to put the code in my .aspx page... But I'm not quite there yet. Thank you all,Kenny.  

View 19 Replies View Related

Converting Empty String To Null When Inserting/updating

Mar 10, 2006

    I am using the following query to calculate date differences:select ..........DATEDIFF(d,  recruitment_advertising.advertising_date, career_details.RTS_Email AS Datetime) AS Ad_to_RTS_days FROM .....I have stored all my dates as NVARCHAR because of the issues with localization.If the value is an empty String my output is eg: -38700. which is way off and incorrect. Some of the values in my table are NULL and they produce the correct result.Is there a T-SQL statement to replace empy Strings with the NULL value in my tables.I'd like to use it as a trigger when inserting or updating to convert empty strings to NULLbefore the values are inserted.Thanks guys.

View 1 Replies View Related

Oracle Empty String == NULL Behavior In SQLServer 2k5?

Feb 15, 2007

I'd like to have Oracle's empty string behavior in SQLServer 2k5. Oracle treats an empty string as NULL's.

In PL/SQL can do:
SELECT * FROM TABLE WHERE TABLE.FIELD IS NULL
... and it'd return rows containing NULL's as well as empty strings.

Can this be done? I couldn't find a setting for it.

Thanx

Peter

View 13 Replies View Related

Anyway To Check If A Text Field Is Blank (not Null But Just A Empty String '') ?

Oct 27, 2004

i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this:


Code:


IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE(''))



Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table

but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression"

thanks

View 2 Replies View Related

Anyway To Check If A Text Field Is Blank (not Null But Just A Empty String '') ?

Oct 27, 2004

i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this:


IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE(''))


Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table

but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression"

thanks

View 3 Replies View Related

Integration Services :: Empty String Is Showing As NULL Value In Database?

Oct 8, 2015

In my source table, I have columns FirstName and LastName, both of datatype nvarchar. In my dataflow, I created two new derived columns mapped to these two columns. When pushing data to the source, I noticed that the FirstName column had a value of NULL while LastName was just an empty string (for rows that did not have any value).

My question is, why my source table column FirstName is showing a NULL value when the derived column datatype is string and the source is string? It should just be showing an empty string right?

View 3 Replies View Related

Cast/Convert Mmddyy In String To New DB_DATETIMESTAMP Column In Derived Column Transformation

Mar 5, 2007

Hi,
I have dates in "mmddyy" format coming from the sources and they are older dates of mid 80s like 082580 for instance.

When I cast it this way (DT_DBTIMESTAMP) Source_Date , It says ok but throws a runtime error.

When I hardcode a date in same format, (DT_DBTIMESTAMP) "082580" , It becomes red (an indication of syntax error) . Please note that we use double quotes in expressions in Derived Column Transformation; So an anticipation that using double quotes over single ones would be the syntax problem would be wrong.



Any help in this will sincerely be appreciated.


Thanks

View 7 Replies View Related

Select Qry: 1 Real Value To Formatted Decimal, 2. Replace NULL With String, How Do I?

Jun 14, 2005

Hi.

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.

View 6 Replies View Related

How To Give The Null Value In The Derived Column Expression Value

Aug 25, 2006

Hi I want to pas one Derived Column Value is "Null" Default . How to Give the Expression "

For Example"

The Derived Column i gave one Column Name



Derived Column Name Expression Data Type



Price ""



Any one give me the Solution

View 1 Replies View Related

Default To NULL Instead Of Blank/empty String During Flat File Import

Aug 15, 2006

Hi,

In SSIS flat file import using fastload, I'm trying to import data into SQL 2005 previously created tables.

The table may contain column that are NULLable BUT there is NO DEFAULT for them.

If the incoming data from flat files contains nothing either between the delimeters, how can I have a NULL value inserted in the column instead of blank/empty string?

I didn't find an easy flag unless I'm doing something wrong. I know of at least two ways to do it the hard way:

1- set the DEFAULT(NULL) for EVERY column that needs this behaviour

2-set up some Derived Column option in the package to return NULL if the value is missing.

Both of the above are time consuming since I'm dealing with many tables. Is there a quick option to default the value to NULL WHEN there is NO data ELSE insert the data itself? So the same behavior that I have right now except that I want NULL in place of empty string/blank in the varchar(x) columns.



Thanks

Anatole

View 9 Replies View Related

What Is The Difference Between Updating Null Value Vs Empty String To Varchar/char Field?

Aug 29, 2007

Hi,
What is the difference updating a null value to char/varchar type column

versus empty string to char/varchar type column?Which is the best to do and why?
Could anyone explain about this?

Example:

Table 1 : tCountry - Name varchar(80) nullable
Table 2 :tState - Name char(2) nullable
Table 3 :tCountryDetails - countryid,state (char(2) nullable) - May the country contain state or no state
So,when the state is not present for the country ,i have two options may be - null,''
tCountryDetails.State = '' or tCountryDetails.State = null?

View 9 Replies View Related

How To Replace Empty Space Or White Space In A String In A Stored Procedure

Nov 14, 2007

Hi,
 I am trying to do this:
UPDATE Users SET  uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl')
What would be the syntax.
Any help appreciated.
Thanks
 

View 1 Replies View Related

How To Assign Null In Derived Column Transformation Editor

Mar 28, 2007

Dear friends, can any one tell me how to assign null to the expression value in derived column transfromation editor?

thanks,

View 5 Replies View Related

Derived Column - DateTimeStamp From String (dd-mmm-yy)

Jun 7, 2006

I have come across something strange. I was wondering anyone else had the same problem or is it me?

I have a source file as shown below:
1,30-Feb-06,3,Test
2,20-Feb-06,5,Test1

Second column is defined as String(9) in the flat file source. Then I have a Derived Column between source and Trash destination. The Derived Column adds a new column to the datd flow. The expression is as shown below :
(DT_DBTIMESTAMP)(SUBSTRING(Col2,1,2) + "/" + SUBSTRING(Col2,4,3) + "/" + SUBSTRING(Col2,8,2))

The data is loading into the database successfully, where I was expecting it to fail. I placed a data viewer and saw the following result set.

Source (Col2) Derived Col
------------------- ------------------
30-Feb-06 06/02/1930 00:00:00 - WRONG result set
20-Feb-06 20/02/2006 00:00:00 - RIGHT result set

Can anyone repeat this problem or am I missing something? I have got SP1 and Hotfix installed.

Thanks

Sutha

View 1 Replies View Related

Replace A String Column With 'Y' Or 'N'

Nov 12, 2007

Hi,

I am trying to replace a string column with 'Y' or 'N' in my SSRS report. I tried the following expressions, but no one works for me.


=IIf(Fields!ExpectedWords.Value Is Null, 'Y', 'N')

=IIf(Fields!ExpectedWords.Value = '', 'Y', 'N')
=IIf(IsDBNull(Fields!ExpectedWords.Value), 'Y', 'N')


Would you please tell me what's wrong? Your suggestions would be appreciated.

Thanks alot.

View 3 Replies View Related

Damn! SQLServer2000 Can't Add A NOT NULL COLUMN Even In One Empty Existing Table!

Jan 8, 2007

Damn! SQLServer2000 can't add a NOT NULL COLUMN even in one emptyexisting table!That is, A is the existing table and it is emtpy, I want to add one NOTNULL COLUMN (col_new) to A using following T-SQL statement, then itwill fail.ALTER TABLE A ADDcol_new varchar(600) NOT NULLGOYou should change it to these statements in SQLServer2000:ALTER TABLE A ADDcol_new varchar(600) NULLALTER TABLE A ALTER COLUMN col_new varchar(600) NOT NULLGOah, ridiculous! right?Fortunately, this stupid behavior is changed in SQLServer2005. Thefirst T-SQL statements works.

View 1 Replies View Related

How To Specify An Empty String As A Default Column Value?

Dec 6, 2007

In the Column Properties window's 'Default Value or Binding' property, how do you specify an empty string?

Thanks.

View 5 Replies View Related

Integration Services :: How To Declare Multiple Derived Column In SSIS Derived Column Task

Jul 22, 2015

how to declare multiple derived columns in SSIS Derived Column Task in one attempt.as i have around 150 columns coming from Flat file. I had created the required Expression in Excel and now i want add those in derived column task but its allowing only 1 expression at a time.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved