Remove the last carriage return and line feed from sql text field

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • whitej77777@gmail.com
    #1

    Remove the last carriage return and line feed from sql text field

    I am trying to write a user defined function that will allow me to
    strip off the last carriage return and line feed from a text field.
    We have address fields stored in a text field for our ERP system and
    some of them have an extra carriage return and line feed at the end of
    them. This causes havoc when we sync between our ERP system and CRM
    system. If anyone knows a way to solve this problem the help would be
    appreciated.

    Examples:
    Existing Text field with CR:

    1234 Blah Street<CR>
    Suite 2345<CR>

    Corrected Text field:

    1234 Blah Street<CR>
    Suitr 2345

    Tags: None
    • Erland Sommarskog
      #2
      Re: Remove the last carriage return and line feed from sql text field

      (whitej77777@gm ail.com) writes:
      I am trying to write a user defined function that will allow me to
      strip off the last carriage return and line feed from a text field.
      We have address fields stored in a text field for our ERP system and
      some of them have an extra carriage return and line feed at the end of
      them. This causes havoc when we sync between our ERP system and CRM
      system. If anyone knows a way to solve this problem the help would be
      appreciated.
      >
      Examples:
      Existing Text field with CR:
      >
      1234 Blah Street<CR>
      Suite 2345<CR>
      >
      Corrected Text field:
      >
      1234 Blah Street<CR>
      Suitr 2345
      SELECT substring(col, 1,
      len(str) - CASE WHEN str LIKE '%' + char(13)
      THEN 1
      ELSE 0
      END)

      1) I've taken you by the word that the character at the end is precisely
      CR. You may find that it is LineFeed (char(10)) or CR+LF.

      2) I did not take you by the word on the data type, but assumed that
      when you said "text" you in fact mean a varchar column. If the data
      type actually is text, I don't know for sure if the above will
      work.


      --
      Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

      Books Online for SQL Server 2005 at
      http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

      Books Online for SQL Server 2000 at
      http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

        Comment

        • whitej77777@gmail.com
          #3
          Re: Remove the last carriage return and line feed from sql text field

          On Jun 12, 5:14 pm, Erland Sommarskog <esq...@sommars kog.sewrote:
          (whitej77...@gm ail.com) writes:
          I am trying to write a user defined function that will allow me to
          strip off the last carriage return and line feed from a text field.
          We have address fields stored in a text field for our ERP system and
          some of them have an extra carriage return and line feed at the end of
          them. This causes havoc when we sync between our ERP system and CRM
          system. If anyone knows a way to solve this problem the help would be
          appreciated.
          >
          Examples:
          Existing Text field with CR:
          >
          1234 Blah Street<CR>
          Suite 2345<CR>
          >
          Corrected Text field:
          >
          1234 Blah Street<CR>
          Suitr 2345
          >
          SELECT substring(col, 1,
          len(str) - CASE WHEN str LIKE '%' + char(13)
          THEN 1
          ELSE 0
          END)
          >
          1) I've taken you by the word that the character at the end is precisely
          CR. You may find that it is LineFeed (char(10)) or CR+LF.
          >
          2) I did not take you by the word on the data type, but assumed that
          when you said "text" you in fact mean a varchar column. If the data
          type actually is text, I don't know for sure if the above will
          work.
          >
          --
          Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
          >
          Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
          Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx- Hide quoted text -
          >
          - Show quoted text -
          I did mean that the data type was actually text which is what is
          probably causing the most problem.

            Comment

            • Erland Sommarskog
              #4
              Re: Remove the last carriage return and line feed from sql text field

              (whitej77777@gm ail.com) writes:
              On Jun 12, 5:14 pm, Erland Sommarskog <esq...@sommars kog.sewrote:
              > (whitej77...@gm ail.com) writes:
              > SELECT substring(col, 1,
              > len(str) - CASE WHEN str LIKE '%' + char(13)
              > THEN 1
              > ELSE 0
              > END)
              >
              I did mean that the data type was actually text which is what is
              probably causing the most problem.
              So did my SELECT work for you?

              I can spot one change that is needed: use datalength() rather than
              len(), as len() does not work past the 8000-character limit.


              --
              Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

              Books Online for SQL Server 2005 at
              http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

              Books Online for SQL Server 2000 at
              http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                Comment

                • whitej77777@gmail.com
                  #5
                  Re: Remove the last carriage return and line feed from sql text field

                  On Jun 15, 5:08 pm, Erland Sommarskog <esq...@sommars kog.sewrote:
                  (whitej77...@gm ail.com) writes:
                  On Jun 12, 5:14 pm, Erland Sommarskog <esq...@sommars kog.sewrote:
                  (whitej77...@gm ail.com) writes:
                  SELECT substring(col, 1,
                  len(str) - CASE WHEN str LIKE '%' + char(13)
                  THEN 1
                  ELSE 0
                  END)
                  >
                  I did mean that the data type was actually text which is what is
                  probably causing the most problem.
                  >
                  So did my SELECT work for you?
                  >
                  I can spot one change that is needed: use datalength() rather than
                  len(), as len() does not work past the 8000-character limit.
                  >
                  --
                  Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
                  >
                  Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
                  Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx
                  Well I modified it slightly to get it to work but thank you for the
                  help. Here is what I used:

                  substring(fmstr eet, 1, datalength(fmst reet) - CASE WHEN fmstreet LIKE
                  '%' + char(13) + char(10) THEN 2 ELSE 0 END)

                  Thanks again.

                    Comment

                    Previous template Next
                    Working...