[thelist] trimming db output (was: ASP question: Req...)
rudy
r937 at interlog.com
Thu Nov 29 18:18:50 CST 2001
anthony:
> When pulling data from a database with ASP, make sure you trim()
> all your data as it comes out of the database.
>
> e.g.
>
> varFieldOne = Trim(objRS("FieldOne"))
>
> You save yourself from debugging hell when the data does not come
> out "clean" from the DB.
okay, i appreciate that this is a "defensive programming" strategy, and in
that sense it's good advice, but sheesh, the time to do the trimming, in
fact the time to do *all* edits to ensure clean data, is on **input**
if you do it on output you'll have redundant code all over the friggin
place
james:
> varFieldOne = Trim(objRS("FieldOne") & "")
>
> This, of course, forces at least an empty string and avoids any
> headaches from nulls. Comments? Counter-suggestions?
this may work in ASP, but if you try the same approach in sql you will get
burned
a null concatentated with anything is still a null
anthony:
> But I don't usually care about NULL specifically. Blank, NULL, Spaces -
> they are all the same to me. Kinda cavalier, I know. ;-)
yup, cavalier is a good way to describe it
i still love ya, though, so feel free to contact me when nulls rise up and
bite you on the ass some day
dex:
> Select ISNULL('n/a', Department) as Department from ...
> (this is MS SQL Server; check your db's documentation
> to see if you have it)
all databases that are sql-2 compliant should have the COALESCE function
the equivalent to the above is
select coalesce(Department, 'n/a') as Department
in oracle it's the NVL() function
anthony:
> At work, I usually am the one to harp on "let the database do the work"
see, i knew you were on the side of goodness and light ;o)
anybody ever tried the NULLIF function?
NULLIF( exp1, exp2 )
returns NULL if exp1 and exp2 are equal, otherwise returns exp1
i still haven't figured out a good place to use this...
rudy
More information about the thelist
mailing list