locked
Using Trim in a Select Query RRS feed

  • Question

  • Greetings Folks,
    I'm trying to create a select query that will trim out extraneous characters from the left & right of a given field, & maintain the remaining data in the field.

    Example:
    Field1 = <p>ABC</p>

    Select
    ltrim(rtrim( Field1,'<p>')'</p>' ) as Field1

    Result set = Field1 = ABC

    My syntax skills have a great deal to go & any help would be greatly appreciated.

    Thank You,

    J.


    JDA

    Thursday, April 30, 2020 5:35 PM

All replies

  • You should use replace function:

    Select
    replace(replace( Field1,'<p>','').'</p>','') as Field1

    from yourtable

    • Proposed as answer by Tom Phillips Thursday, April 30, 2020 6:06 PM
    Thursday, April 30, 2020 5:47 PM
  • I hope you read the SQL standards and know the field is a subset of a column. However, it is better to edit your data before you put it in the database instead of after, you will find other languages that were designed for string handling will do a much better job than we can SQL. I also think that you need a CHECK() constraint to preserve data integrity.  

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, April 30, 2020 7:41 PM
  • Hi JDArsenault, 

    As you said that 'create a select query that will trim out extraneous characters from the left & right of a given field, & maintain the remaining data in the field' , What is the meaning of extraneous characters? I think it also contain '/','@','$' and so on . Can you specify what your data look like?

    As I known, above script can get your result  and REPLACE is a good way.  If you would like to maintain the remaining data, how would you like to maintain ? Just SELECT ,or UPDATE ?  Please share us more information . Thanks in advance .

    Best Regards,

    Rachel 



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 1, 2020 7:14 AM