SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query to get first sentence from field

    I've got a text field from which I'd like to extract the first sentence for each record. Here's the query I've got so far.

    SELECT SUBSTRING_INDEX(j.job_desc,'.',1) AS job_desc
    FROM jobs

    The problem is that in some of the records, the first sentence is terminated by a question mark (or exclamation point) rather than a period and I need to get those sentences as well. I'm trying to find out how I can say "the first sentence" instead of "the first sentence ending in a period".

    Does anyone have any ideas?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  2. #2
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Erm,

    CREATE TABLE terminators(char CHAR(1));
    INSERT INTO terminators VALUES('.'), ('?'), ('!');

    Then a cross join to find the minimum length?

    Or use REPLACE(job_desc, '?', '.') etc before the SUBSTRING_INDEX

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    "Stop!" said Mary.
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know, I know Rudy. But this isn't going to be that sort of field. It's a job description; no literary nonsense here. 99% of the text will be like this:

    Counsel On Call is actively interviewing outstanding paralegals wanting to work on contract assignments in the Atlanta metro area.

    East Memphis multidiscipline Law Firm is looking for an associate to assist in the growth of its Family Law practice.

    Memphis firm is seeking a paralegal to work in its family practice group.

    Etc...

    So any ideas?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    ren's REPLACE sounds like a good start
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •