[AnalyticLunatic]

I have a table View in in my Database on Microsoft SQL Server. In SS Management Studio I am trying to modify this view to meet the requirements needed when it is used in an SSIS package to push data onto the MySQL Server.

I'm trying to check several fields for having a length greater than zero.

Examples for field (LastName):

'madison' = GOOD
'm' = GOOD
'' = FAIL
NULL = FAIL

I need a way to check that the field has a character in it, not just say a blank space should a user start to enter the value, then backspace it clear. This action results in a blank space instead of a NULL value, but neither are allowed on the MySQL Server.

Does anyone have any ideas? My SQL is still pretty low-level.

[modi123_1]

You could also trim the left and right ends and get the count of how many characters are left.. and use an 'isnull' to say "hey if this value is null then make it blank!".

len(rtrim(ltrim(isnull(@foo, ''))))

[AnalyticLunatic]

modi123_1, on 13 February 2013 - 08:52 PM, said:

You could also trim the left and right ends and get the count of how many characters are left.. and use an 'isnull' to say "hey if this value is null then make it blank!".

len(rtrim(ltrim(isnull(@foo, ''))))

Thanks for the reply modi! It's all being checked in the WHERE clause (The view is simply a select statement being used in the SSIS package to select particular records for processing into the MySQL Server).

Right now my query ends with:

 WHERE     (StatusText = 'Active') AND (LastName IS NOT NULL)

Would the right/left trims work in this case? I need criteria for 'NOT NULL' AND having characters (not just a blank space).

[modi123_1]

The trims would remove any spaces on the sides.. so " " would just be "". The isnull replacement would ensure you are not messing with nulls as strings... and the len would check for significant values..

so yeah.. it would work.

    declare @foo VARCHAR(20)
   set  @foo = null
    
    select 1 
    where len(rtrim(ltrim(isnull(@foo, '')))) = 0

[AnalyticLunatic]

Thanks modi! Everything appears to be working as intended now with the following:

WHERE     (StatusText = 'Active') AND 
(LastName IS NOT NULL) AND (LEN(RTRIM(LTRIM(LastName))) > 0) AND 
(RegUIDNumber IS NOT NULL) AND (LEN(RTRIM(LTRIM(RegUIDNumber))) > 0) AND 
(FirstName IS NOT NULL) AND (LEN(RTRIM(LTRIM(FirstName))) > 0) AND 
(Address1Text IS NOT NULL) AND (LEN(RTRIM(LTRIM(Address1Text))) > 0) AND 
(CityText IS NOT NULL) AND (LEN(RTRIM(LTRIM(CityText))) > 0) AND 
(StateCodeText IS NOT NULL) AND (LEN(RTRIM(LTRIM(StateCodeText))) > 0) AND 
(ZipCodeText IS NOT NULL) AND (LEN(RTRIM(LTRIM(ZipCodeText))) > 0) AND 
(CountyDescrText IS NOT NULL) AND (LEN(RTRIM(LTRIM(CountyDescrText))) > 0) AND
(AffidavitBit IS NOT NULL) AND (LEN(RTRIM(LTRIM(AffidavitBit))) > 0) AND 

Appreciate it Good Sir! If you might know a cleaner way to write that, I'm all ears, but it DOES do the job.

EDIT: Reformatted that in the preview to where it was easier to read, then when posting it got all messed up again. Sorry for that

This post has been edited by AnalyticLunatic: 13 February 2013 - 02:27 PM

[andrewsw]

If you follow the example provided by modi123_1 then you wouldn't need the additional IS NOT NULL terms.

I believe it would also work with:

WHERE NULLIF(LTRIM(RTRIM(@foo)), '') IS NOT NULL

but there is no advantage

I did try something like NOT NULL IN (@foo, @bar) but (of course!) we cannot compare null this way - that's why we need IS NULL or ISNULL().

[e_i_pi]

Your predicates can be streamlined pretty heavily, in two particular aspects:

• LEN(NULL) is NULL, which cannot be compared to a numeric, but does so with graceful degradation. In other words LEN(NULL) > 0 returns false, so a check to see if the value is NULL is moot.
  
• For values that are entirely whitespace (which I believe is what you want to preclude), the command RTRIM(LTRIM(Value)) yields the same result as LTRIM(Value).

So, you can rewrite your predicate like so, without any loss of meaning, but executing faster:

WHERE (StatusText = 'Active') AND 
(LEN(LTRIM(LastName)) > 0) AND 
(LEN(LTRIM(RegUIDNumber)) > 0) AND 
(LEN(LTRIM(FirstName)) > 0) AND 
(LEN(LTRIM(Address1Text)) > 0) AND 
(LEN(LTRIM(CityText)) > 0) AND 
(LEN(LTRIM(StateCodeText)) > 0) AND 
(LEN(LTRIM(ZipCodeText)) > 0) AND 
(LEN(LTRIM(CountyDescrText)) > 0) AND
(LEN(LTRIM(AffidavitBit)) > 0) AND 

[AnalyticLunatic]

andrewsw, on 13 February 2013 - 10:54 PM, said:

If you follow the example provided by modi123_1 then you wouldn't need the additional IS NOT NULL terms.

I believe it would also work with:

WHERE NULLIF(LTRIM(RTRIM(@foo)), '') IS NOT NULL

but there is no advantage

I did try something like NOT NULL IN (@foo, @bar) but (of course!) we cannot compare null this way - that's why we need IS NULL or ISNULL().

modi's precise example would have been feasible, but I'm not using any variables in this particular query. Thanks for the reply though!

e_i_pi, on 13 February 2013 - 11:20 PM, said:

Your predicates can be streamlined pretty heavily...

You can rewrite your predicate like so, without any loss of meaning, but executing faster:

WHERE (StatusText = 'Active') AND 
(LEN(LTRIM(LastName)) > 0) AND 
(LEN(LTRIM(RegUIDNumber)) > 0) AND 
(LEN(LTRIM(FirstName)) > 0) AND 
(LEN(LTRIM(Address1Text)) > 0) AND 
(LEN(LTRIM(CityText)) > 0) AND 
(LEN(LTRIM(StateCodeText)) > 0) AND 
(LEN(LTRIM(ZipCodeText)) > 0) AND 
(LEN(LTRIM(CountyDescrText)) > 0) AND
(LEN(LTRIM(AffidavitBit)) > 0) AND 

Thanks e_i_pi! That appears to be just what I was after. I changed everything over to that new set-up, and came to a crashing halt with the overall project. One of those 'It works PERFECT in TEST! Let's try on PRODUCTION!.....'



I was hoping to be kind of a hotshot and complete this modification on my own without calling on the more experienced coworkers, but alas, their help was needed. On the bright(ish) side, we are still trying to figure out what is wrong, so it's not just me!

This post has been edited by AnalyticLunatic: 14 February 2013 - 01:34 PM

[andrewsw]

Quote

modi's precise example would have been feasible, but I'm not using any variables in this particular query.

Erm, yes, but you would replace those variables with field-names.

Never mind.. good luck, Andy.

This post has been edited by andrewsw: 14 February 2013 - 01:42 PM

[baavgai]

You can leave off the ltrim or rtrim, you only need one to check. You don't have to mess with the null, a null value will not be > 0.

Also, try to write the field once...

WHERE StatusText = 'Active'
	AND LEN(LTRIM(LastName)) > 0
	AND LEN(LTRIM(RegUIDNumber)) > 0
	AND LEN(LTRIM(Address1Text)) > 0
	AND LEN(LTRIM(CityText)) > 0
	AND LEN(LTRIM(StateCodeText)) > 0
	AND LEN(LTRIM(ZipCodeText)) > 0
	AND LEN(LTRIM(CountyDescrText)) > 0
	AND LEN(LTRIM(AffidavitBit)) > 0

Alternately, since adding a null will make the whole bloody thing null:

WHERE StatusText = 'Active'
	AND CharIndex('~~', IsNull(
		'~' + LTRIM(LastName)
		'~' + LTRIM(RegUIDNumber)
		'~' + LTRIM(Address1Text)
		'~' + LTRIM(CityText)
		'~' + LTRIM(StateCodeText)
		'~' + LTRIM(ZipCodeText)
		'~' + LTRIM(CountyDescrText)
		'~' + LTRIM(AffidavitBit)
		'~', '~~'))!=0

However, I strongly recommend just cleaning up your data.

e.g.

update MessyTable set LastName = RTRIM(LTRIM(LastName))
update MessyTable set LastName = null where Len(LastName)=0

Then indexes and all that good stuff work better.