CODEWITHSUNDEEP
sqlsql-server
jaiparkumar
jaiparkumar

Reputation: 57

sql server help to avoid string

How to remove alpha character from string field in sql server?

CREATE TABLE [dbo].[orders](
    [orderid] [varchar](50) NULL,
    [ordernumber] [varchar](100) NULL
) 
INSERT [dbo].[orders] ([orderid], [ordernumber]) VALUES (N'1', N'ab123c2')
INSERT [dbo].[orders] ([orderid], [ordernumber]) VALUES (N'2', N'4xHx32a')
INSERT [dbo].[orders] ([orderid], [ordernumber]) VALUES (N'3', N'h2b58B21b22')

based on above data I want output like below :

orderid | Ordernumber
1       | 1232
2       | 432
3       | 2582122

I tried like below

SELECT
    orderid
   ,REPLACE( REPLACE ( ordernumber, 'a', ''), 'b', '') AS ordernumber
FROM orders

here replace I need to use almosst 26 times for lower and upper case letters.

could you please tell me how to write query easy way to resolve this issue in sql server

Upvotes: 0

Views: 230

Answers (1)

Thom A
Thom A

Reputation: 96004

You intially tagged SQL Server 2016 and 2012, which means that I'm posting 2 answers here. The first is "nice and easy"; use TRANSLATE to change the characters to a specific character, and then replace them all:

SELECT orderid,
       REPLACE(TRANSLATE(ordernumber,'abcdefghijklmnopqrstuvwxyz',REPLICATE('a',26)),'a','') AS ordernumber
FROM dbo.orders O;

If you're on SQL Server 2012, then you would need to use nested replaces. Otherwise, you could do something that seems unintuitive, but does work. Split the string into individual characters, and then rebuild it, but only with the characters you want; in this case non-letters:

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2)
SELECT O.orderid,
       (SELECT SUBSTRING(O.ordernumber,T.I,1) 
        FROM Tally T
        WHERE T.I <= LEN(O.ordernumber)
          AND SUBSTRING(O.ordernumber,T.I,1) NOT LIKE '[A-Za-z]'
        FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(100)')
FROM dbo.orders o;

if you wanted to just retain the numbers, then replace NOT LIKE '[A-Za-z]' with LIKE '[0-9]'.

If you really wanted, you could turn the above into a FUNCTION, and then use that:

CREATE FUNCTION dbo.RemovePattern (@String varchar(8000), @Pattern varchar(8000)) 
RETURNS varchar(8000)AS
BEGIN
    DECLARE @Return varchar(8000);
    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT TOP(LEN(@String)) 
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3, N N4)
    SELECT @Return = (SELECT SUBSTRING(@String,T.I,1) 
                      FROM Tally T
                      WHERE SUBSTRING(@String,T.I,1) NOT LIKE @Pattern
                      FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(100)');

    RETURN @Return;

END;
GO

SELECT orderid,
       dbo.RemovePattern(O.ordernumber,'[A-Za-z]') AS NewOrderNumber
FROM dbo.orders O;

db<>fiddle

Upvotes: 1

Related Questions