Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2010
    Posts
    5

    Unanswered: removing blank space in select query

    I have a table with below definition

    SQL> desc service_connection;
    Name Null? Type
    -------------------------------------- -------- ------------------
    COL1 NOT NULL NUMBER(38)
    COL2 VARCHAR2(20)
    COL3 VARCHAR2(200)

    When i run a select query on this table i get the blank space also.. how can i remove this space ?

    select '''',col1,'''','''',col2,'''','''',col3,'''' from service_connection;

    output:
    'col1 ', ' col2 ', 'col3 '

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >When i run a select query on this table i get the blank space also.. how can i remove this space ?

    Since we can not actually "see" the blank space, you need to tell us exactly where this blank space occurs.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2010
    Posts
    5
    I gave the spaces but text formatting removed it . Below is the output of the select query.

    select '''',col1,'''','''',col2,'''','''',col3,'''' from service_connection;
    HTML Code:
    output:
    'col1       ', '       col2 '                     , 'col3                                        '

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    use LTRIM & RTRIM
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2010
    Posts
    5
    No effect..i tried using ltrim , rtrim and both at same time.
    Please let me know if i am not using it correctly.
    Below is the output when i used rtrim .

    HTML Code:
    select '''',rtrim(col1),'''','''',rtrim(col2),'''' from service_connection sc where rownum = 1;
    
    ' Col1value               ' ' Col2value                                                                                   '

  6. #6
    Join Date
    Jun 2010
    Posts
    5
    below query resolved the issue.

    select '''' || col1 || '''' || ',' || '''' || col2 || '''' from service_connection;

    output:
    'col1value','col2value'

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool or this...

    Quote Originally Posted by swapnesh View Post
    below query resolved the issue.

    select '''' || col1 || '''' || ',' || '''' || col2 || '''' from service_connection;

    output:
    'col1value','col2value'
    or this:
    Code:
    SELECT '''' || col1 || ''',''' || col2 || '''' FROM service_connection;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >COL1 NOT NULL NUMBER(38)
    Since COL1 is a number, Why enclose it in single quote marks?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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