Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2012
    Posts
    8

    Unanswered: Trimming of blob data

    Hello,

    I need to trim (removing white spaces) the data or text in a column which is of type IMAGE.

    I would like to achieve this through a PL/SQL procedure or function.

    Please let me know the solution as early as possible.

    Kind regards,
    Subbu.
    Last edited by nbsubbaiah; 03-14-12 at 02:07.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Is this an MSSQL Server question, or an Oracle question?

  3. #3
    Join Date
    Mar 2012
    Posts
    8
    It is MSSQL question. Sorry for mentioning the word 'blob'

    Basically need to trim the text data stored in the image column of a table.

  4. #4
    Join Date
    Mar 2012
    Posts
    8

    URGENT: Need to trim the text data stored in image column

    Hello,

    I need to trim (removing white spaces) the data or text stored in an IMAGE column.

    I need a PL/SQL procedure or function as early as possible.

    Can anyone throw some light on this?

    Kind regards,
    Subbu.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    How do you define "white space" in an image? The data is stored as a binary string.

  6. #6
    Join Date
    Mar 2012
    Posts
    8
    Before inserting some text into the image column, that text may have for example, a tab or a space or a new line character at the end of the text.

    So I want to trim those white space characters which are already present in the image column and update the refined text back into that image column.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Start by changing the datatype to varchar(max).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Mar 2012
    Posts
    8
    You mean to change before reading? or change the column data type??
    Changing of the column datatype is a ruled out option for me atleast.

    Please guide me in building up a PL/SQL function or procedure.

  9. #9
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by blindman View Post
    Start by changing the datatype to varchar(max).
    Do they have that datatype in Oracle?

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The IMAGE datatype is simply hauled back and forth on behalf of an application. The data is treated as a byte stream, and SQL won't manipulate it.

    You need to either:

    1) Clean up the data in your application(s)

    or

    2) Store the data in a column with an appropriate data type (like blindman's suggestion of VARCHAR(MAX) which is conceptually very similar to IMAGE).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Mar 2012
    Posts
    8
    First thank you both (PatP and blindman).

    Your suggestions would be helpful while designing the database itself. But currently my Customer is holding and having this problem.

    I need to provide a work-around to hime asap. Kindly guide me in that direction!!

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by corncrowe View Post
    Do they have that datatype in Oracle?
    Oracle calls it a CLOB (Character Large OBject).

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Completely untested:
    Code:
    SELECT your_blob
         , Convert(varchar(max), your_blob)
         , RTrim(Convert(varchar(max), your_blob))
         , Convert(image, RTrim(Convert(varchar(max), your_blob)))
    FROM   your_table
    George
    Home | Blog

  14. #14
    Join Date
    Mar 2012
    Posts
    8
    gvee, it didnt work!!
    Because, explicit conversion from data type image to varchar(max) is not allowed.

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You really need to fix the underlying problem that your client has created for themselves. Leaving it "as is" and fixing this specific underlying problem does them a gross dis-service.

    With that said, if you want to milk your client for all that they're worth:
    Code:
    CREATE TABLE #foo (
       i		IMAGE
       )
    
    INSERT #foo (i)
       SELECT Cast('This is a test, with trailing blanks          ' AS VARBINARY)
    
    SELECT
       QUOTENAME(Cast(Cast(i AS VARBINARY) AS VARCHAR))
    ,  QUOTENAME(RTrim(Cast(i AS VARBINARY)))
       FROM #foo
    
    DROP TABLE #foo
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

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