Oracle REGEXP_REPLACE equivalent in SQLSERVER

Asked

Viewed 80 times

-1

In my application I need to remove common string words of the database, such as: FROM TO WITH

Which are connecting words and have no great importance in characterization of the item itself.

Say the item:

COMMAND POST

I can easily remove the DE thus: 

REPLACE( 'CASA DE COMANDO, ' DE ', ' ' )

But how the ' DE ' is between two whites if he appears at the beginning or at the end replace won’t work. Then I replace the command with one like this:

   REPLACE( REPLACE( REPLACE( 'CASA DE COMANDDO, ' DE ', ' ' ), ' DE', ' ' ), 'DE ', ' ' )

It removes the common word when it is between spaces, at the end or at the beginning.

The problem is that if a word starts or ends with OF it will withdraw as well. I need the replace respect the whole word and not just part of it.

In the ORACLE I resolved with REGEXP_REPLACE, but I couldn’t find anything similar native in the SQLSERVER.

Write a UDF to solve this kind of out of the question because it’s the whole routine makes many comparisons between the strings and it takes a long time, if I add even more that time I’m afraid to make the programme impossible.

Someone already solved that question and can help me?

sql-server-2012

  • Here your problem requires a better explanation.

  • @Ernestocasanova I think the explanation is enough. I need to exchange the word DE without changing the part of a word that contains DE, for example, DECANTING TANK. I need to change only the first DE. The function should return DECANTING TANK. This is explained in the question and that’s it. Note: And it was not I who denied your answer.

  • I get the feeling of lack info, is not the explanation of replace, I think it was clear, lack of context, for example, you will use then in SQL JOB, periodically and need to create an SP, or for each input you receive want to replace, or want in queries in a VIEW to do this replace and show the strings with the format you want and never change the data in the database. You talk a routine, but run.

  • It is your impression. If you solve me as I use the SELECT REPLACE( ... I can use in anything. In fact, this replace must be part of a function. I pass the string to the function and it returns the clean string to me, without these link words.

1 answer

0

I created this function to solve the above problem. O REGEX_REPLACE oracle has been spun into 3 Places to achieve the same goal.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON


GO
-- =============================================
-- Author:      <Reginaldo Rigo>
-- Create date: <30/03/2020>
-- Description: <Retirar todas as palavras comum constantes na tabela de palavras comum
-- da string que for passada como parametro.>
-- =============================================
ALTER FUNCTION retiracomuns
(
    @strlimpa varchar(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @palavra varchar(MAX)
    DECLARE palavras CURSOR FOR select palavra from PALAVRASCOMUNS
    OPEN palavras
    FETCH NEXT FROM palavras INTO @palavra
    WHILE @@FETCH_STATUS = 0
    BEGIN 

        /*
        //   Essa parte em Oracle 

        strlimpa := REGEXP_REPLACE( strlimpa, '(^|\s)' || rec.palavra || '(\s|$)',' ');
        //
        */


       /* Foi substituida por essa em SQLSERVER */ 

       /* trato o primeiro caso particular quando a string começa com a palavra comum */
       IF PATINDEX( @palavra + ' %', @strlimpa ) > 0 
          set @strlimpa = SUBSTRING( @strlimpa, len(@palavra) + 2, LEN( @strlimpa ))


        /* trato o segundo caso particular quando a string termina com a palavra comum */
       IF PATINDEX( '% ' + @palavra, @strlimpa ) > 0 
           set @strlimpa = SUBSTRING( @strlimpa, 1,  PATINDEX( '% ' + @palavra, @strlimpa ))

       /* aqui posso usar o replace normal */
       /* como a palavra esta em dois espaços a função replace não irá mexer em outros lugares */
       set @strlimpa = RTRIM( LTRIM( REPLACE( @strlimpa, ' ' + @palavra + ' ', ' ' )))


       FETCH NEXT FROM palavras INTO @palavra 
    END
    CLOSE palavras
    DEALLOCATE palavras



    RETURN @strlimpa

END
GO

Browser other questions tagged sql-server-2012

You are not signed in. Login or sign up in order to post.