SQL query..

  • vickriz
  • Novice
  • Novice
  • User avatar
  • Posts: 31
  • Loc: Phlippines

Post 3+ Months Ago

Hi to all, i dont know where to post this but i give a shot here..

i have a table in ACCESS2000 with the field of sufixName.
my problem is they mixes with some other characters..
how can i eliminate or trim-out the other string aside from this... Jr., Sr. I, II, III and IV?

anybody knows how to do it with SQL statement? please help
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • b_heyer
  • Web Master
  • Web Master
  • User avatar
  • Posts: 4581
  • Loc: Maryland

Post 3+ Months Ago

I'm not exactly sure what you mean, but if you try to clearify I might be able to help...
  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23456
  • Loc: Woodbridge VA

Post 3+ Months Ago

If I understand you correctly, which I'm not certain I do, these are some examples of how TRIM can be used:
Code: [ Select ]
SELECT TRIM('  wamalamadingdong ');
-> 'wamalamadingdong'

SELECT TRIM(LEADING '19' FROM '1976 AMC GREMLIN');
-> '76 AMC GREMLIN'

SELECT TRIM(BOTH 'x' FROM 'xxxWHISKEYxxx');
-> 'WHISKEY'

SELECT TRIM(TRAILING 'snack' FROM 'scooby snack');
-> 'scooby '
  1. SELECT TRIM('  wamalamadingdong ');
  2. -> 'wamalamadingdong'
  3. SELECT TRIM(LEADING '19' FROM '1976 AMC GREMLIN');
  4. -> '76 AMC GREMLIN'
  5. SELECT TRIM(BOTH 'x' FROM 'xxxWHISKEYxxx');
  6. -> 'WHISKEY'
  7. SELECT TRIM(TRAILING 'snack' FROM 'scooby snack');
  8. -> 'scooby '


So in your case let's say you have, Henry Clay Jr.

Code: [ Select ]
SELECT TRIM(TRAILING 'JR.' FROM 'Henry Clay Jr.');
-> 'Henry Clay'
  1. SELECT TRIM(TRAILING 'JR.' FROM 'Henry Clay Jr.');
  2. -> 'Henry Clay'


etc...

Obviously, you want to keep Jr., so you would apply this to the ones you don't wish to keep (I just used Jr. as an example). Your question isn't totally clear, so I hope that comes close to what you are looking for.

The SQL99 syntax for TRIM is:

TRIM( [ [{LEADING | TRAILING | BOTH}] [removal_string] FROM ]
target_string
[COLLATE collation_name])

MySQL, PostgreSQL, and Oracle support the SQL99 syntax of TRIM.
  • vickriz
  • Novice
  • Novice
  • User avatar
  • Posts: 31
  • Loc: Phlippines

Post 3+ Months Ago

b_heyer wrote:
I'm not exactly sure what you mean, but if you try to clearify I might be able to help...


for simplicity heres some records in my table:

Field:SuffixName
row1:Jr.
row2:Sr.
row3:Edison, IV. Hyde
row4:Terry
row5:Shane, Sr. Von
row6:B.
row7:G.
row8:III.
row9:Jr.
row10:T.
row11:L.

so after the update query from the table, the result in this table should be:

Field:SuffixName
row1:Jr.
row2:Sr.
row3:IV.
row4:
row5:Sr.
row6:
row7:
row8:III.
row9:Jr.
row10:
row11:

I hope this is clear now. by the way the table has composite of other fields such as IDs and Names.. and it has 200,000records..
  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23456
  • Loc: Woodbridge VA

Post 3+ Months Ago

I'm sure others will give a more exact answer, but this is where I'm at in my PHP and SQL education, so I'm sure if I tried to give an exact example, I'd screw up the syntax, but my thought would be something along the lines of an IF statement or CASE - perhaps something along these lines:

Code: [ Select ]
IF ($row != JR. && $row != Sr. && $row != I && $row != II, etc.)
  {
   $row = NULL;
  }
ELSE
  {
   RETURN;
  }
  1. IF ($row != JR. && $row != Sr. && $row != I && $row != II, etc.)
  2.   {
  3.    $row = NULL;
  4.   }
  5. ELSE
  6.   {
  7.    RETURN;
  8.   }


I'm sure that's not 100% right, but I think the idea may work if the syntax is correct. Or if you want the change to be a permanent update to the table, then run an UPDATE and set any rows not equal to the ones you want to retain to NULL. I imagine you'll have to run it as a loop to make all the changes, and that's still beyond my education.

Post Information

  • Total Posts in this topic: 5 posts
  • Users browsing this forum: No registered users and 75 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.