Jump to content
Fórum Script Brasil
  • 0
Sign in to follow this  
jothaz

CHARINDEX - localizando e extraindo delimitadores em string

Question

DECLARE  @TESTE TABLE (NUMERO INT, OBS VARCHAR(100), CODIGO_LIMPO VARCHAR(1000))
INSERT INTO @TESTE (NUMERO,OBS) VALUES (1,'MARIA FOI A FEIRA [22.22.22] E COMPROU [ 22.55.55] ')
INSERT INTO @TESTE (NUMERO,OBS) VALUES (2,'[2.10.AA] O ALVO É MOVEL')
INSERT INTO @TESTE (NUMERO,OBS) VALUES (3,'[2.10.AC] O ALVO É MOVEL')
INSERT INTO @TESTE (NUMERO,OBS) VALUES (4,'[2.10.SA] O ALVO[ED.DE.DE] É MOVEL')
INSERT INTO @TESTE (NUMERO,OBS) VALUES (5,'[2.10.VA] O ALVO [11.1.11]É MOVEL')

DECLARE  @RESULTADO TABLE (CODIGO_LIMPO VARCHAR(1000))

DECLARE @NUMERO INT, @OBS VARCHAR(100)
DECLARE MYCURSOR CURSOR FOR
SELECT NUMERO,OBS FROM @TESTE 
OPEN MYCURSOR
FETCH NEXT FROM MYCURSOR INTO @NUMERO,@OBS

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @DELI_INI VARCHAR(5), @DELI_fim VARCHAR(5) ,@CONT INT, @AUX VARCHAR(200), @POS_INI INT, @POS_FIM INT

    -- SETANDO O DELIMITADOR
    SELECT @DELI_INI = '[', @DELI_fim = ']'

    SELECT @OBS = LTRIM(RTRIM(@OBS))


    WHILE LEN(@OBS) > 0
        BEGIN SELECT @POS_INI = CHARINDEX(@DELI_INI, @OBS)
        SELECT @POS_FIM = CHARINDEX(@DELI_FIM, @OBS)
        
        IF @POS_INI > 0 AND @POS_FIM > 0
        BEGIN
            SELECT @CONT = (@POS_FIM - @POS_INI) + 1
            SELECT @AUX = SUBSTRING(@OBS, @POS_INI, @CONT)

            --PARA JOGAR NO CAMPO_LIMPO UTILIZE O UPDATE A SEGUI
            INSERT INTO  @RESULTADO (CODIGO_LIMPO) VALUES (@AUX)
            SELECT @OBS = SUBSTRING(@OBS, @POS_FIM + 1, LEN(@OBS))
        END
        ELSE
        BEGIN
            BREAK
        END
    END
    FETCH NEXT FROM MYCURSOR INTO @NUMERO,@OBS
END
CLOSE MYCURSOR
DEALLOCATE MYCURSOR


SELECT * FROM @RESULTADO

Considerações finais:

O CHARINDEX em conjunto com as funções: SUBSTRIGN, LEN, CASE e outras permite a manipulação de string´s como qualquer outra linguagem de programação.

Fonte/Autor/link

AUTOR: "Jothaz"

Dúvidas, criticas, contribuições, correções e adições serão bem vindas.

Edited by jothaz

Share this post


Link to post
Share on other sites

0 answers to this question

Recommended Posts

There have been no answers to this question yet

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  



  • Forum Statistics

    • Total Topics
      148409
    • Total Posts
      643823
×
×
  • Create New...