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


ADVERTISEMENT

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

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

Pass In Null/blank Value In The Date Field Or Declare The Field As String And Convert

Dec 30, 2003

I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.

I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable

mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))

option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.

With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End

Please help

View 6 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

SQL 2012 :: Blank (Empty String) In Primary Key

Apr 29, 2014

Can we have blank value in Primary key field? Two fields are chosen as Primary Key for this table but the issue is at any given time one value will be blank when other is populated. Does sql server still order the records based on these keys? (Clustered index). Or should I just go about adding ID (identity)?

View 1 Replies View Related

Bcp Is Inserting Blank Space For Empty String

Jul 23, 2005

I'm doing a bcp out of a table to a file. Some of the fields in arecord may have an empty string.When I bcp out to the file and examine it, the fields that have anempty string in the database now show up in the file as having oneblank character.Why is bcp doing this? I don't want the blank character in my output.Thanks,Eric

View 4 Replies View Related

Empty String Equals To Blank: How To Change This Behaviour?

Feb 11, 2008

This seems to be a rather old problem (http://www.themssforum.com/SQLServer/Does-empty/) but I couldn't find an answer yet.

The problem is: I have two tables t1 and t2 where t1 is a staging area of t2.
t1: (id int not null, phone varchar(30))
t2: (id int not null, phone varchar(30))

Data in t1: (1, '') <- empty string
Data in t2: (1, ' ') <- a blank

Comparing t1.phone with t2.phone results in equality which in my opinion isn't correct.

The question ist: How can I change the behaviour of SQL-Server to result in inequality so that the change in my staging table is detected correctly?

Thanks in advance
Fridtjof

View 4 Replies View Related

T-SQL (SS2K8) :: Check If Variable Is Empty Or Null?

Sep 9, 2014

declare @user varchar(30) = ''
if(@user is not null or @user <> '')
BEGIN
print 'I am not empty'
END
ELSE
BEGIN
print 'I am empty'
ENd

The output should be 'i am empty' but when i execute this i am getting ' i am not empty'. even i did browse through but i don't find the difference in my logic.

View 9 Replies View Related

Check For Null Or Empty Values And Return 0

Mar 13, 2014

I am using the below query to calculate column values. But I need to return zero when a column values is empty or null.

select [Funding] [Fundings],
[Original] AS [Originals],
[Variance] = SUM([Previous_Year]-[Current_Year]),
[SumValue] = SUM([CurrentYear]/4),
[ActualValue] = SUM([Variance] * 0.75),
[FinanceYear],
[New Value] = SUM([Previous_Year]+[Current_Year])
from Finance
GROUP BY [Original], [FinanceYear]

View 1 Replies View Related

How To Check For Null / Empty For A Variable In Post Deployment Script

Oct 28, 2015

I am using SSDT to deploy my database project. This project has a post deployment script and i am creating a new user in this post deployment script. I have used sqlcmd variable to capture the username. I am doing a null/empty check for this variable and my code looks like below.

IF (([$(UserName)] IS NOT NULL) OR ([$(UserName)] <> ''))
BEGIN
CREATE USER [$(UserName)] WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo;
GRANT CONNECT TO [$(UserName)];
END

When i execute this, I am getting below error.

Could not deploy package.

Error SQL72014: .Net SqlClient Data Provider: Msg 207, Level 16, State 1,Line 15 Invalid column name 'User Name'.

Error SQL72045: Script execution error. The executed script:

View 2 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

Insert Previous Record In A Blank Or A Null Field

Nov 29, 2000

Data from as400 imports into SQL with blank fields which is the way as400 outputs records. How can you insert previous record of data null or blank field. ex:

ONETWO
a1
2
3
b1
2
3

Would want:

ONETWO
a1
a2
a3
b1
b2
b3

View 5 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

T-SQL (SS2K8) :: Field Has No Value But Is Not NULL Or Empty?

Aug 13, 2015

I added a new field to an existing ETL process which uses SSIS to ingest a CSV file. The new field in the file, Call_Transaction_ID, will not always be populated for every record and so can be NULL or empty for certain records.

Here's the problem:After the file is extracted into a staging table, the Call_Transaction_ID field is showing blank or empty when it has no ID for that particular record. The problem is when I try to then ETL this data into a fact table - I'm trying to set the Call_Transaction_ID field to -1 if it is NULL or empty, however SQL Server doesn't see the field as empty even though there is no value in the field so -1 will NEVER return.

Using a WHERE DATALENGTH(Call_Transaction_ID) = 0 returns 0 records, again because SQL Server doesn't see the field as empty or NULL.

What do I do now to get around this? How do I fix it?

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

Text Field Comes Back Blank When Using ADO

Apr 2, 2008



Originally, I hit this problem with PHP. But then I was able to reproduce using VBScript.

I have a table with a couple of text fields, and one Real field in a table. One row of data in the table. Both text fields set, but the real field doesn't even need to have a value set.

Connecting to SQL 2005 using a DSN - ODBC.

I'm using a stored procedure I named ExecIt that runs a SQL statement that is sent in and returns records.

If I use cursor location adUseClient, it returns values for the text fields.

If I use asUseServer, it returns blank values.

If I use a SQL statement directly, it works. Only fails when using SQL statement by way of ExecIt.

In each case, cursor type is adOpenStatic or adOpenDynamic.

Using this combination fo cursor type and cursor location, it mimics what I see in SQL profiler when PHP is using ODBC.

Anyone have a solution?

Table:

CREATE TABLE [dbo].[test3](

[ID] [int] IDENTITY(1,1) NOT NULL,

[text1] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[text2] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[real1] [real] NULL,

CONSTRAINT [PK_ReworkLog2] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Stored Procedure - ExecIt:


create PROCEDURE [dbo].[ExecIt]

-- Add the parameters for the stored procedure here

@Statement nvarchar(max)

AS

BEGIN

exec (@Statement)

END

DSN has been set up through ODBC manager. Can be using 2005 native driver, or older sql server driver.

Code:

dim txtOut

set conn1=CreateObject("ADODB.Connection")
conn1.Open "my-DSN","my-userid","my-password"
sql="exec execit 'select * from test3 where ID=1'"
set rs = CreateObject("ADODB.recordset")
rs.CursorLocation = 2 'adUseServer - 2, adUseClient - 3
rs.CursorType = 3 'adOpenStatic
rs.Open sql,conn1
if not rs.EOF then
txtOut = "Results" & vbcrlf
txtOut = txtOut &"text1:" & rs("text1") & vbcrlf
txtOut = txtOut &"text2:" & rs("text2") & vbcrlf
txtOut = txtOut & vbcrlf
end if
msgbox txtOut
rs.Close
conn1.Close

View 3 Replies View Related

Parameter Value From Empty Text Box - Null Or 0 Length?

Jul 20, 2005

Hi,I run a stored procedure with a parameter given from a text box in an accessadp. If the text box is empty then what is passed to the parameter? I can'tseem to get it to flag up as either null, or 0 length.Any tips?Cheers,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

How To Pass Null Value To The Database If A Form Field Is Empty?

May 31, 2004

How can I pass into the database (@User_fax = null) if the fax form field is empty, from a command type Stored Procedure? For example:

Dim CmdUpdate As New SqlCommand("Form2_NewUser", strConnection)
CmdUpdate.CommandType = CommandType.StoredProcedure

CmdUpdate.Parameters.Add("@User_fax", SqlDbType.char, 9)
CmdUpdate.Parameters("@User_fax").Value = fax.Text()

...

strConnection.open()
CmdUpdate.ExecuteNonQuery
strConnection.close()



And, the stored procedure inside Sql server:

USE market1
GO
ALTER PROC Form2_NewUser
@User_id bigint, @User_fax char(9),...

AS

SET NOCOUNT ON
UPDATE Users
SET User_fax = @User_fax, ...

WHERE User_id = @User_id
SET NOCOUNT OFF
GO



Thank you,
Cesar

View 4 Replies View Related

IS NULL Returns Empty Records (using TEXT Type)

Mar 3, 2008

Hi all I am having some issues in selecting items from my database where the record is NOT NULL. I have the code below however although some fields do contain soem data in it, others are blank which I believe are empty spaces. How do I do a SELECT command which ignores empty spaces and NULLS?





Code Snippet

SELECT CustomSearch FROM OfficesTable WHERE CustomSearch IS NOT NULL
Thanks, Onam.

View 10 Replies View Related

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 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

Setting Empty Text Box Input On Form To Null Parameter For Sql

Feb 19, 2007

Hi,
 I'm pretty new to ASP.NET and VB, but I'm working on a project which is essentially a staff directory. Using VS 2005, I've setup a basic grid view which connects to an object which connects via a data layer to a SQL database with Name, Surname, Email, Extension No and Department fields.
I have it working so that if a user enters a name and surname for example, it will return all records with either the name matching or surname matching input parameters. What I want to do is to set up a SQL query which is:
 SELECT * FROM records WHERE (Name LIKE @Name) AND (Surname LIKE @Surname) AND (Email LIKE @Email) AND (Dept LIKE @Dept)
so that if the user only enters the first name and surname for example as above, it ONLY returns the record which matches the first name and surname and not all records with either/or. One way I've thought of to do this is to convert the empty fields to "Nothing" so that it fullfils the search parameter for the empty fields. If someone can explain how to do this, or can suggest a better way, I'd be grateful.
Cheers,
Tom

View 3 Replies View Related

Please Check This 'not Null' SQL String

Jun 20, 2007

the SQL string below worked, and then started bringing up every record.
it should only select records with a value in at least one of the columns, but it apears to be suggesting that all records have some data in one of the columns. if I check the database or the output on the web page there apears to be no data. ?? confused.

"SELECT id, make, model FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL"

Any ideas how I could implement this more robustly?
cheers
M

View 14 Replies View Related

How Do I Check If A Field Is Null?

Jul 2, 2007

hi guys, I'm using SQLServer 2005 and I was wondering how I check if some variable is null or not? I tryied to do as the code show but it doesn't work:

declare @number int

set @number = (select number from numbers where number_id = 1)

if (@number = null)
begin
set @number = 12
end

Thank you very much

View 3 Replies View Related

SQL - Updating Empty Text Field Is Enterring -1

Jul 20, 2005

I'm trying to do a simple update like I have done countless timesbefore.However, when I update the empty fields in this table it places a -1into the field rather than the enterred value.It is indexed with duplicates allowed. It says that zero lenght isset to yes.What can I do about this?if Request.form("ouserid") <> "" Thenvar2 = " and userid = '" & Request.form("ouserid") & "' "Elsevar2 = " and userid is null "End ifif Request.form("orepid") <> "" Thenvar1 = " repid = '" & Request.form("orepid") & "'"Elsevar1 = " repid is null "End if"Update [Website Settings] set userid = '" & REquest.form("userid") &"' and repid = '" & Request.form("repid") & "' where " & var1 & var2& var3Thanks!

View 2 Replies View Related

SQL Server 2012 :: Insert Empty String In A Decimal Field?

Jul 13, 2015

I have a requirement where i have to insert empty string in column whose datatype is decimal.

I have to replace the column value if it is null then insert it as ' '.

Obviously its generate error msg for conversion, is there any workaround for this. I cannot add 0 or other value as this column is used to generate some output value.

IIF(Column1 IS NULL, ' ', Column1))

I used TRY_PARSE but it insert null value if there is conversion error.

View 3 Replies View Related







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