ok
so you want to do an update on the table
try this first
SELECT name,
address,
Replace(mobileNumber,' ','') AS Mobile,
Len(Replace(mobileNumber,' ','')) AS MobileLen
FROM thisTable
WHERE mobileNumber LIKE '% %'
That will give you all the records that have spaces, the number with spaces
removed and also the length
If the result is to your liking, then use that to do your update
UDATE thisTable
SET MobileNumber = Replace(mobileNumber,' ','')
WHERE mobileNumber LIKE '% %'
Now just to mention other databases. If there is one thing I loved with
mySQL is that you can use regular expressions, so you can do this
UDATE thisTable
SET MobileNumber = REPLACE(MobileNumber,'REGEXP "[^[:digit:]]"','')
WHERE mobileNumber LIKE '% %'
I like that
Steve
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Adam
Chapman
Sent: Monday, July 07, 2003 5:47 PM
To: CFAussie Mailing List
Subject: [cfaussie] Re: Replace in SQL rather that CF
>> Which DB?
Ah yes.. Small detail?! MSSQL 2000
> Adam Chapman <[EMAIL PROTECTED]> wrote:
>
> Hey cfers (and sqlers)..
>
> Can this be done in SQL?? Ive looked thru
> Some docs and tried a few things.. But..
>
> SELECT name, address
> FROM thisTable
> WHERE Len(Replace(mobileNumber,' ','')) = 10
>
> Basically I want to strip spaces from mobile
> Numbers and make sure there are 10 digits..
> I don't really want to loop thru each number
> And test its length in CF..
>
> Now sql like a pig... ;P
>
> Regards,
>
> Adam Chapman
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MX Downunder AsiaPac DevCon - http://mxdu.com/
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MX Downunder AsiaPac DevCon - http://mxdu.com/
