hey guys.
this is a 2 parter.
i have a table i need to fix. i have no real idea how to do this. the
problem is the Contact filed.
the first several thousand entries combine fnme lname into this one field
from a xls file.
then maybe 20,000 entries only have a name with a space in the Contact
but what if i want
like '% ';
and
not like '% money'
where money could be any last name or character.
On Jan 3, 2008 10:17 AM, Greg Morphis [EMAIL PROTECTED] wrote:
the SQL statement like requires a %..
for example..
select * from froo where name like 'G%'
will return all names that starts
the SQL statement like requires a %..
for example..
select * from froo where name like 'G%'
will return all names that starts with G..
So try something like
select * from tbl where name like '% ';
That will catch anything with a trailing space.
Just a heads up..
On Jan 3, 2008 9:03 AM, morchella
'% ' shouldnt return ' money'
it should only return enteries with a trailing space..
for example
create table testtbl ( name varchar2(10));
insert into testtbl values ('Greg ');
insert into testtbl values ('Greg M');
insert into testtbl values ('Greg Mo');
insert into testtbl values ('Gary ');
You may need to get into database character functions. I believe they
all have them, but they all implement them slightly differently. You
will need to consult appropriate documentation for you database
management system.
But you should be able to do something like this concept.
SELECT
just fond out why
man this db is so messed up...
'Joe Garth '
so i would i look for NOT LIKE '% % '
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
On 1/3/08, Paul Ihrig [EMAIL PROTECTED] wrote:
just fond out why
man this db is so messed up...
'Joe Garth '
so i would i look for NOT LIKE '% % '
OR...
ltrim(rtrim(contact)) NOT LIKE '% %'
~|
Adobe® ColdFusion® 8
If what you are trying to do is eliminate trailing spaces why not just do:
Update contacts set contact = rtrim(ltrim(contact))
-Original Message-
From: morchella [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 03, 2008 9:03 AM
To: CF-Talk
Subject: sql question: contains space
ok..
thank you!
SELECT id, Contact, Address, City, State, Zip
FROM Leads
WHERE (Contact LIKE '') OR
(Contact LIKE ' ') OR
(Contact NOT LIKE '% % ')
AND (LTRIM(RTRIM(Contact)) NOT LIKE '% %')
ORDER BY id
now to fix 20,000+ records!
Mark
i am trying to find where only the lastname was added to the field Contact.
some 2000+ records. then do a match on a known field like email or phone
then update the Contact field with the combined 'fname lname' from a xls
spread sheet.
then after all the names are combined i will just add 2
10 matches
Mail list logo
