Remove hyphen from end of string in SQL Server-CodePudding

I'd like to remove hyphen from string values using SQL.

I have a column that contains data like:

middle-to high-income parents
Assets -
business – 1 year in their Certified program
explain your assets
10-12-15 years -

and this is what I need from those string:

middle-to high-income parents
Assets
business – 1 year in their Certified program
explain your assets
10-12-15 years

I tried

rtrim(ltrim(replace(bal_sheet_item, '-', ' ')))

but it removed all hyphens not just the ones at the end of the string.

CodePudding user response：

You can accomplish this with a little gymnastics, however why not just have your presentation layer remove any trailing dashes?

SELECT bal_sheet_item = LEFT(bal_sheet_item, LEN(bal_sheet_item) 
   - CASE WHEN RIGHT(bal_sheet_item,1) = '-' THEN 1 ELSE 0 END)
FROM 
(
  SELECT bal_sheet_item = RTRIM(bal_sheet_item), 
         lenA = LEN(bal_sheet_item)
  FROM dbo.YourTable
) AS x;

In newer versions you can just say:

SELECT bal_sheet_item = TRIM('- ' FROM bal_sheet_item)
FROM dbo.YourTable;

Working example in this fiddle.

CodePudding user response：

@JavaLifeLove - It would be helpful if, when you posted data examples, you posted them as readily consumable data, like the following...

--===== If the temp table exists, drop it to make reruns easier in SSMS.   
   DROP TABLE IF EXISTS #TestTable;
GO
--===== Create and populate the temp table on-the-fly for such simple data.
     -- Do a separate CREATE TABLE and an INSERT/VALUES for something more complex.
 SELECT v.*
   INTO #TestTable
   FROM (VALUES
         ('middle-to high-income parents')
        ,('Assets -')
        ,('business – 1 year in their Certified program')
        ,('explain your assets')
        ,('   10-12-15 years - ')
        )v(bal_sheet_item)
;
GO

That'll not only help explain things but it will help the people that are trying to help you because a lot of them like to actually test their code before they publish a possible answer to your question. It'll entice people to help more quickly, as well. :D

I'd also be checking out who the source of the data is coming from. People don't just causally end something with a dash. It can be a pretty strong indication that something got dropped along the way and I'd let someone know about that possibility before writing any code to possibly perpetuate the bad data.

If they say "Just do it", then I'd turn a solution into a bit of repeatable code that others can use because you're probably not the only one that will need to deal with such garbage data.

With that being said, here's a high performance iTVF (inline Table Valued Function) using one of many methods to solve this problem. The usage example is in the flower box of the function.

 CREATE FUNCTION dbo.DropTrailingCharacter
/**********************************************************************************************************************
 Purpose:
 Remove the last given @pCharacter from the given @pString even if @pCharacter is followed by trailing spaces. 
 The final result is trimmed for both leading and trailing spaces.
 Works for 2012 .
-----------------------------------------------------------------------------------------------------------------------
 WARNING:
 Modifying original data may not be the correct thing to do. For example, people don't arbitrarily add dashes to the
 end of data. It may be an indication that a part of the original data may be missing. Check with the people that are
 providing the data to ensure that's not what's happening.
-----------------------------------------------------------------------------------------------------------------------
 Usage Example:
--===== Test table.  This is not a part of the solution. It's just an example for usage.
   DROP TABLE IF EXISTS #TestTable
;
 SELECT v.*
   INTO #TestTable
   FROM (VALUES
         ('middle-to high-income parents')
        ,('Assets -')
        ,('business – 1 year in their Certified program')
        ,('explain your assets')
        ,('   10-12-15 years - ')
        )v(bal_sheet_item)
;
--===== Remove single trailing dash even if followed by spaces.
 SELECT  tt.bal_sheet_item
        ,bal_sheet_item_cleaned = ca.Cleaned
   FROM #TestTable tt
  CROSS APPLY dbo.DropTrailingCharacter(tt.bal_sheet_item,'-')ca
;
-----------------------------------------------------------------------------------------------------------------------
 Revision History:
 Ref 00 - 30 Oct 2022 - Jeff Moden
        - Initial creation and unit test.
        - Ref: https://stackoverflow.com/questions/74255805/remove-hyphen-from-end-of-string-in-sql-server/74256248
**********************************************************************************************************************/
        (@pString VARCHAR(8000), @pCharacter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN 
 SELECT Cleaned = LTRIM(RTRIM(
                    IIF(RIGHT(ca.Trimmed,1) = @pCharacter
                       ,LEFT(ca.Trimmed,LEN(ca.Trimmed)-1) --Character found and removed from end of of string.
                       ,ca.Trimmed)))                      --Character not found. Keep everything
   FROM (VALUES(RTRIM(@pString)))ca(Trimmed)               --DRYs out the code above.
;
GO