Find substring from string in sql

Question

I am using SQL SERVER 2008.I have following string in sql.

DECLARE @stringRTF VARCHAR(MAX)

set @stringRTF = '{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Tahoma;}} \viewkind4\uc1\pard\f0\fs18 \v [\TEST\*#70_250_263] \v0 \par '

Now i want to find string "\TEST*#" from above string. if it is present in the full string then i want to cut string [\TEST*#70_250_263] from full string.

So it should be return like

[\TEST\*#70_250_263]

as return string.

So can any one help me out how to find and cut that string from original string?

Thanks in advance.

You could use LIKE or REGEXP (as in "WHERE field LIKE '%[\TEST*#70_250_263]%' or "WHERE field REGEXP 'TEST'") and then actually perform the extraction in code, or you could use PREG_CAPTURE as is shown here: regular-expressions.info/mysql.html (if you're using MySQL. MSSQL might support a REGEXP_SUBSTR like Oracle. — SubSevn
– SubSevn, Commented May 17, 2013 at 10:04

Dhwani · Accepted Answer · 2013-05-17 10:31:34Z

2

I think below code should work:

DECLARE @stringRTF VARCHAR(1000), @stringFind VARCHAR(30), @startIndex 

int,@outputString varchar(100)

set @stringRTF = '{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Tahoma;}} \viewkind4\uc1\pard\f0\fs18 \v [\TEST\*#70_250_263] \v0 \par '

SET @stringFind = '\TEST\*#'
SET @startIndex =  CHARINDEX(@stringFind, @stringRTF) - 1
SET @outputString = SUBSTRING(@stringRTF , @startIndex,  len(@stringRTF))
SET @outputString = LEFT(@outputString , CHARINDEX(']',@outputString) )
PRINT @outputString --Your result

Tell me if you face any error.

edited May 17, 2013 at 10:31

answered May 17, 2013 at 10:08

Dhwani

7,66618 gold badges82 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Devart · Accepted Answer · 2013-05-17 10:29:33Z

1

Try this one -

DECLARE @stringRTF VARCHAR(1024)
SELECT @stringRTF = 
    '{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Tahoma;}} \viewkind4\uc1\pard\f0\fs18 \v [\TEST\*#70_250_263] \v0 \par '

DECLARE @res VARCHAR(100)

SELECT @res = SUBSTRING(
    @stringRTF, 
    CHARINDEX('\TEST\*#', @stringRTF) - 1, 
    CHARINDEX(']', @stringRTF) - CHARINDEX('\TEST\*#', @stringRTF) + 2 
)

PRINT @res

Output:

[\TEST\*#70_250_263]

@ITBeginner, change this rows in your code:

'\TEST*#' to '\TEST\*#'
@stringRTF.length to LEN(@stringRTF)

edited May 17, 2013 at 10:29

answered May 17, 2013 at 10:19

Devart

120k23 gold badges172 silver badges189 bronze badges

2 Comments

Dhwani Over a year ago

Nice one. My answer have don't know what's the problem.

Devart Over a year ago

@ITBeginner, happy to help you. To make to make fewer syntax errors try to use any auto-completion toolsб like this: devart.com/dbforge/sql/sqlcomplete

Collectives™ on Stack Overflow

Find substring from string in sql

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related