[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