Help with query

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Help with query

Mike Ealy-2
I need help constructing a MySQL query. I'm fairly new to writing queries
and I'm having a problem with this one.

Right now I'm holding Social Security numbers in a table that I need to
delete, not the entire record, just the SSNs.

The field contains some Social Security numbers as well as non-SSNs. The
non-SSNs are seven digits or less. The real SSNs sometimes appear formatted
as 123-45-6789, 123456789, and 123 45 6789.

How do I delete the real SSNs, regardless of format, without removing
anything with seven digits or less.

Here's what I have so far.

SELECT table1.ID,
    table1.SchoolNum,
    table1.SSN
FROM table1
WHERE table1.SchoolNum = '2323' AND CHARACTER_LENGTH(SSN)>8

I know this isn't really the place to be asking but I'm not too sure where
else to ask something like this.

Thanks so much.

Mike Ealy
Clearwater, FL


============================================
Attend the Lasso Summit
March 2-4, 2007 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Help with query

Brett Harvey
if the query you had finds everything you want, then do

update table1 SET SSN="" WHERE table1.SchoolNum = '2323' AND CHAR_LENGTH(SSN)>8

Brett

>I need help constructing a MySQL query. I'm fairly new to writing queries
>and I'm having a problem with this one.
>
>Right now I'm holding Social Security numbers in a table that I need to
>delete, not the entire record, just the SSNs.
>
>The field contains some Social Security numbers as well as non-SSNs. The
>non-SSNs are seven digits or less. The real SSNs sometimes appear formatted
>as 123-45-6789, 123456789, and 123 45 6789.
>
>How do I delete the real SSNs, regardless of format, without removing
>anything with seven digits or less.
>
>Here's what I have so far.
>
>SELECT table1.ID,
>     table1.SchoolNum,
>     table1.SSN
>FROM table1
>WHERE table1.SchoolNum = '2323' AND CHARACTER_LENGTH(SSN)>8
>
>I know this isn't really the place to be asking but I'm not too sure where
>else to ask something like this.
>
>Thanks so much.
>
>Mike Ealy
>Clearwater, FL
>
>
>============================================
>Attend the Lasso Summit
>March 2-4, 2007 in Fort Lauderdale, FL
>http://www.LassoSummit.com/
>============================================
>
>------------------------------
>Lasso Support: http://support.omnipilot.com/
>Search the list archives: http://www.listsearch.com/lassotalk.lasso
>Manage your list subscription:
>http://www.listsearch.com/lassotalk.lasso?manage


============================================
Attend the Lasso Summit
March 2-4, 2007 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Help with query

stevepiercy
In reply to this post by Mike Ealy-2
Why not just delete the column?

ALTER TABLE table1 DROP SSN;

--steve


On Thursday, January 25, 2007, [hidden email] (Mike Ealy) pronounced:

>I need help constructing a MySQL query. I'm fairly new to writing queries
>and I'm having a problem with this one.
>
>Right now I'm holding Social Security numbers in a table that I need to
>delete, not the entire record, just the SSNs.
>
>The field contains some Social Security numbers as well as non-SSNs. The
>non-SSNs are seven digits or less. The real SSNs sometimes appear formatted
>as 123-45-6789, 123456789, and 123 45 6789.
>
>How do I delete the real SSNs, regardless of format, without removing
>anything with seven digits or less.
>
>Here's what I have so far.
>
>SELECT table1.ID,
>    table1.SchoolNum,
>    table1.SSN
>FROM table1
>WHERE table1.SchoolNum = '2323' AND CHARACTER_LENGTH(SSN)>8
>
>I know this isn't really the place to be asking but I'm not too sure where
>else to ask something like this.
>
>Thanks so much.
>
>Mike Ealy
>Clearwater, FL
>
>
>============================================
>Attend the Lasso Summit
>March 2-4, 2007 in Fort Lauderdale, FL
>http://www.LassoSummit.com/
>============================================
>
>------------------------------
>Lasso Support: http://support.omnipilot.com/
>Search the list archives: http://www.listsearch.com/lassotalk.lasso
>Manage your list subscription:  
>http://www.listsearch.com/lassotalk.lasso?manage
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy               Web Site Builder               Soquel, CA
<[hidden email]>                   <http://www.StevePiercy.com>

============================================
Attend the Lasso Summit
March 2-4, 2007 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Help with query

Brett Harvey
Steve,

You missed part of his post ;)    "without removing anything with
seven digits or less."

Brett

>Why not just delete the column?
>
>ALTER TABLE table1 DROP SSN;
>
>--steve
>
>
>On Thursday, January 25, 2007, [hidden email] (Mike Ealy)
>pronounced:
>
>>I need help constructing a MySQL query. I'm fairly new to writing queries
>>and I'm having a problem with this one.
>>
>>Right now I'm holding Social Security numbers in a table that I need to
>>delete, not the entire record, just the SSNs.
>>
>>The field contains some Social Security numbers as well as non-SSNs. The
>>non-SSNs are seven digits or less. The real SSNs sometimes appear formatted
>>as 123-45-6789, 123456789, and 123 45 6789.
>>
>>How do I delete the real SSNs, regardless of format, without removing
>>anything with seven digits or less.
>>
>>Here's what I have so far.
>>
>>SELECT table1.ID,
>>     table1.SchoolNum,
>>     table1.SSN
>>FROM table1
>>WHERE table1.SchoolNum = '2323' AND CHARACTER_LENGTH(SSN)>8
>>
>>I know this isn't really the place to be asking but I'm not too sure where
>>else to ask something like this.
>>
>>Thanks so much.
>>
>>Mike Ealy
>>Clearwater, FL
>>
>>
>>============================================
>>Attend the Lasso Summit
>>March 2-4, 2007 in Fort Lauderdale, FL
>>http://www.LassoSummit.com/
>>============================================
>>
>>------------------------------
>>Lasso Support: http://support.omnipilot.com/
>>Search the list archives: http://www.listsearch.com/lassotalk.lasso
>>Manage your list subscription:
>>http://www.listsearch.com/lassotalk.lasso?manage
>-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>Steve Piercy               Web Site Builder               Soquel, CA
><[hidden email]>                   <http://www.StevePiercy.com>
>
>============================================
>Attend the Lasso Summit
>March 2-4, 2007 in Fort Lauderdale, FL
>http://www.LassoSummit.com/
>============================================
>
>------------------------------
>Lasso Support: http://support.omnipilot.com/
>Search the list archives: http://www.listsearch.com/lassotalk.lasso
>Manage your list subscription:
>http://www.listsearch.com/lassotalk.lasso?manage


============================================
Attend the Lasso Summit
March 2-4, 2007 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Help with query

Mike Ealy-2
In reply to this post by Brett Harvey
Thanks, Brett, that did it.

> if the query you had finds everything you want, then do
>
> update table1 SET SSN="" WHERE table1.SchoolNum = '2323' AND
> CHAR_LENGTH(SSN)>8
>
> Brett
>
>> I need help constructing a MySQL query. I'm fairly new to writing queries
>> and I'm having a problem with this one.
>>
>> Right now I'm holding Social Security numbers in a table that I need to
>> delete, not the entire record, just the SSNs.
>>
>> The field contains some Social Security numbers as well as non-SSNs. The
>> non-SSNs are seven digits or less. The real SSNs sometimes appear formatted
>> as 123-45-6789, 123456789, and 123 45 6789.
>>
>> How do I delete the real SSNs, regardless of format, without removing
>> anything with seven digits or less.
>>
>> Here's what I have so far.
>>
>> SELECT table1.ID,
>>     table1.SchoolNum,
>>     table1.SSN
>> FROM table1
>> WHERE table1.SchoolNum = '2323' AND CHARACTER_LENGTH(SSN)>8
>>
>> I know this isn't really the place to be asking but I'm not too sure where
>> else to ask something like this.
>>
>> Thanks so much.
>>
>> Mike Ealy
>> Clearwater, FL
>>


============================================
Attend the Lasso Summit
March 2-4, 2007 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Help with query

stevepiercy
In reply to this post by Brett Harvey
My inclination is to store SSNs not at all, or encrypted at least.  Hence DROP SSN.  My background in public education IT has increased my awareness of the improper storage of personal identifying information and its consequences.

Of course dropping the column may break the application that uses that column, in which case there are bigger issues that need to be dealt with.

--steve


On Thursday, January 25, 2007, [hidden email] (Brett Harvey) pronounced:

>Steve,
>
>You missed part of his post ;)    "without removing anything with
>seven digits or less."
>
>Brett
>
>>Why not just delete the column?
>>
>>ALTER TABLE table1 DROP SSN;
>>
>>--steve
>>
>>
>>On Thursday, January 25, 2007, [hidden email] (Mike Ealy)
>>pronounced:
>>
>>>I need help constructing a MySQL query. I'm fairly new to writing queries
>>>and I'm having a problem with this one.
>>>
>>>Right now I'm holding Social Security numbers in a table that I need to
>>>delete, not the entire record, just the SSNs.
>>>
>>>The field contains some Social Security numbers as well as non-SSNs. The
>>>non-SSNs are seven digits or less. The real SSNs sometimes appear formatted
>>>as 123-45-6789, 123456789, and 123 45 6789.
>>>
>>>How do I delete the real SSNs, regardless of format, without removing
>>>anything with seven digits or less.
>>>
>>>Here's what I have so far.
>>>
>>>SELECT table1.ID,
>>>     table1.SchoolNum,
>>>     table1.SSN
>>>FROM table1
>>>WHERE table1.SchoolNum = '2323' AND CHARACTER_LENGTH(SSN)>8
>>>
>>>I know this isn't really the place to be asking but I'm not too sure where
>>>else to ask something like this.
>>>
>>>Thanks so much.
>>>
>>>Mike Ealy
>>>Clearwater, FL
>>>
>>>
>>>============================================
>>>Attend the Lasso Summit
>>>March 2-4, 2007 in Fort Lauderdale, FL
>>>http://www.LassoSummit.com/
>>>============================================
>>>
>>>------------------------------
>>>Lasso Support: http://support.omnipilot.com/
>>>Search the list archives: http://www.listsearch.com/lassotalk.lasso
>>>Manage your list subscription:
>>>http://www.listsearch.com/lassotalk.lasso?manage
>>-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>>Steve Piercy               Web Site Builder               Soquel, CA
>><[hidden email]>                   <http://www.StevePiercy.com>
>>
>>============================================
>>Attend the Lasso Summit
>>March 2-4, 2007 in Fort Lauderdale, FL
>>http://www.LassoSummit.com/
>>============================================
>>
>>------------------------------
>>Lasso Support: http://support.omnipilot.com/
>>Search the list archives: http://www.listsearch.com/lassotalk.lasso
>>Manage your list subscription:
>>http://www.listsearch.com/lassotalk.lasso?manage
>
>
>============================================
>Attend the Lasso Summit
>March 2-4, 2007 in Fort Lauderdale, FL
>http://www.LassoSummit.com/
>============================================
>
>------------------------------
>Lasso Support: http://support.omnipilot.com/
>Search the list archives: http://www.listsearch.com/lassotalk.lasso
>Manage your list subscription:  
>http://www.listsearch.com/lassotalk.lasso?manage
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy               Web Site Builder               Soquel, CA
<[hidden email]>                   <http://www.StevePiercy.com>

============================================
Attend the Lasso Summit
March 2-4, 2007 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage