SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Exporting blob / text column with hyphen delimited text

    Hi all!

    I have a table that contains a column (blob, text type, formatted as MEDIUMTEXT) with text hyphen (-) delimited, listing

    rooms by numbers, and I need to export or insert that data into another table, where all other info on the other columns

    remains the same, but I need the data on the blob field to be single listed

    another words, i have a table like this (example):

    School Rooms
    Your Hometown High School 1034-1035-1037-1039
    My Hometown High School 208-178-1432-1728

    I need it like this:

    School Rooms
    Your Hometown High School 1034
    Your Hometown High School 1035
    Your Hometown High School 1037
    Your Hometown High School 1039
    My Hometown High School 208
    My Hometown High School 178
    My Hometown High School 1432
    My Hometown High School 1728

    Suggestions? Thanks!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    is it always four words delimited by spaces, then four numbers delimited by dashes?

    because if so, you can do it right in sql, using the SUBSTRING_INDEX function a few times
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    Quote Originally Posted by r937 View Post
    is it always four words delimited by spaces, then four numbers delimited by dashes?

    because if so, you can do it right in sql, using the SUBSTRING_INDEX function a few times

    No, not always four numbers, could be 3, 4, 5 numbers, and some cases numbers and letters (ie 1456Z), but always delimited by dashes.

    Thanks!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    probably too messy for sql -- write a script
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937 !


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
  •