Converting 00000000 to null

Article View: bit.databases.mssql-l

Article #2015

Back to bit.databases.mssql-l Previous Next

#2015

From: Richard Green
Date: Mon, 03 Apr 1995 00:00

21 lines
1091 bytes

I have a table (very large) with SEVERAL columns where the value MAY
contain char '00000000'.  These values need to be converted to null
but I need to do this in one pass using SQL and would like to avoid
row by row processing.

The following gets close but does not work.

update tablename
set col1 = isnull(stuff(col1, charindex('00000000', col1), 8, null), col1),
col2 ... same function but use col2

If I set the null in the above to say 'null', then the 00000000s get replaced
with a literal 'null' but this does not help much.  The value needs to be
an actual null.


I would appreciate any help


Richard Green
rg@cpsu.com.au

Technical Details

Message-ID: <9504030921.AA15295@spsfdev2.cpsu.com.au.cpsu.com.au>

Path: arc1-ca.pugleaf.net!unknown!not-for-mail

Show Full Headers

Complete Article Headers

X-Google-Language: ENGLISH,ASCII-7-bit
X-Google-Thread: f6611,389888b4f4338c12,start
X-Google-Attributes: gidf6611,public
From: Richard Green <rg@CPSU.COM.AU>
Subject: Converting 00000000 to null
Date: 1995/04/03
Message-ID: <9504030921.AA15295@spsfdev2.cpsu.com.au.cpsu.com.au>
X-Deja-AN: 100850614
sender: Microsoft SQL Server Discussion List
comments: Gated by NETNEWS@AUVM.AMERICAN.EDU
newsgroups: bit.databases.mssql-l