CODEWITHSUNDEEP
sqlsql-servert-sql
Vaishali Gupta
Vaishali Gupta

Reputation: 1

Remove unwanted characters from a string with SQL

I tried a query to get the below result from a string. But its not showing the accurate result.

I tried using replace function. But I am unable to remove the next hyphen as I have to use the first one. 

DECLARE @str NVARCHAR(MAX);
DECLARE @lpcounter INT;

SET @str = 'ty-R ';
SET @lpcounter = 0;

WHILE @lpcounter <= 26
BEGIN
    SET @str = REPLACE(@str, CHAR(65 + @lpcounter), '');
    SET @lpcounter = @lpcounter + 1;
END;

SELECT @str;

Can this be done through a query only?

Upvotes: 0

Views: 3863

Answers (4)

Alan Burstein
Alan Burstein

Reputation: 7918

I like @Sami's TRANSLATE Solution which, if I were on SQL Server 2017+, I would likely use. Another efficient option would be to use PatExclude8K (DDL included below).

-- Sample Data
DECLARE @table TABLE (string VARCHAR(1000));
INSERT @table VALUES ('8-R1-WEL'),('276-R1E')

-- Solution
SELECT 
  OldString = t.string, 
  NewString = IIF(f.NewString LIKE '%-',SUBSTRING(f.NewString,0,LEN(f.NewString)),f.NewString)
FROM   @table AS t
CROSS APPLY dbo.PatExclude8K(t.string,'[^0-9-]') AS f;

Results:

OldString    NewString
------------ -------------
8-R1-WEL     8-1
276-R1E      276-1

PatExclude8K code:

CREATE FUNCTION dbo.PatExclude8K
(
    @String VARCHAR(8000),
    @Pattern VARCHAR(50)
) 
/*******************************************************************************
 Purpose:
 Given a string (@String) and a pattern (@Pattern) of characters to remove, 
 remove the patterned characters from the string.

Usage:
--===== Basic Syntax Example
 SELECT CleanedString 
 FROM dbo.PatExclude8K(@String,@Pattern);

--===== Remove all but Alpha characters
 SELECT CleanedString 
 FROM dbo.SomeTable st
 CROSS APPLY dbo.PatExclude8K(st.SomeString,'%[^A-Za-z]%');

--===== Remove all but Numeric digits
 SELECT CleanedString
 FROM dbo.SomeTable st
 CROSS APPLY dbo.PatExclude8K(st.SomeString,'%[^0-9]%');

 Programmer Notes:
 1. @Pattern is not case sensitive (the function can be easily modified to make it so)
 2. There is no need to include the "%" before and/or after your pattern since since we 
    are evaluating each character individually

 Revision History:
 Rev 00 - 10/27/2014 Initial Development - Alan Burstein

 Rev 01 - 10/29/2014 Mar 2007 - Alan Burstein
 - Redesigned based on the dbo.STRIP_NUM_EE by Eirikur Eiriksson
   (see: http://www.sqlservercentral.com/Forums/Topic1585850-391-2.aspx)
 - change how the cte tally table is created 
 - put the include/exclude logic in a CASE statement instead of a WHERE clause
 - Added Latin1_General_BIN Colation
 - Add code to use the pattern as a parameter.

 Rev 02 - 11/6/2014
        - Added final performane enhancement (more cudo's to Eirikur Eiriksson)
        - Put 0 = PATINDEX filter logic into the WHERE clause

Rev 03 - 5/16/2015
        - Updated code to deal with special XML characters
*******************************************************************************/
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH
E1(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N)),
itally(N) AS 
(
  SELECT TOP(CONVERT(INT,LEN(@String),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
  FROM E1 T1 CROSS JOIN E1 T2 CROSS JOIN E1 T3 CROSS JOIN E1 T4
) 
SELECT NewString =
((
  SELECT SUBSTRING(@String,N,1)
  FROM iTally
  WHERE 0 = PATINDEX(@Pattern,SUBSTRING(@String COLLATE Latin1_General_BIN,N,1))
  FOR XML PATH(''),TYPE
).value('(text())[1]','varchar(8000)'));
GO

Upvotes: 0

Ilyes
Ilyes

Reputation: 14928

Here you go

WITH C AS
(
SELECT REPLACE(
         TRANSLATE(V, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', REPLICATE(' ', 26) --Or SPACE(26)
         )
         , ' ', '') Res
FROM
(
  VALUES
  ('8-R1-WEL'),
  ('276-R1E')
) T(V)
)
SELECT CASE WHEN RIGHT(Res, 1) = '-'
            THEN LEFT(Res, LEN(Res) -1)
            ELSE Res
       END Result
FROM C;

Demo

Finally, I would recommend doing string manipulation using a other programming language instead of doing it in the database.

Upvotes: 2

Dwight Reynoldson
Dwight Reynoldson

Reputation: 960

Here is one method using patindex to find alpha characters in the string, iterate over that string and then use the length of the string to form a loop to remove these.

DECLARE @str NVARCHAR(MAX);
DECLARE @replacestring nvarchar(max);
declare @stop int = 0;
SET @str = '276-R1E';

select @stop=len(@str);

while @stop>0
begin
select @replacestring = substring(@Str, patindex('%[a-z]%', @str), 1);
select @str = replace(@str,@replacestring, '');
select @stop-=1;
end

SELECT @str;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This returns the values that you specify in the question:

select str, replace(left(str, charindex('-', str) + 2), 'R', '')
from (values ('8-R1-WEL'), ('276-R1E')) v(str);

You haven't expressed the logic, so this does the following:

  • Take the first two substrings separated by hyphens.
  • Remove the "R".

Upvotes: 0

Related Questions