x

I need to get the sub string in between the nth & mth occurrence of a character in a single select statement

Hi,
I need to get the sub string in between the nth & mth occurrence of a character.
Example,from the sting '1:12.85:3:4' I need the sub string in between the first & second occurrence of the character ':' in a single select statement.

Thanks in advance..
Regards,
Nidheesh
more ▼

asked Jul 19, 2011 at 08:33 PM in Default

nidheesh gravatar image

nidheesh
90 11 13 14

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
Your question is the same as this one so the answers will apply to your situation http://ask.sqlservercentral.com/questions/73911/query-to-select-a-string-between-two-known-strings
more ▼

answered Jul 20, 2011 at 02:13 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(comments are locked)
10|1200 characters needed characters left

As I posted several times on this site, the easiest way to solve such scenarios is to use the CLR RegEx implementation.

You can take a look on the sample implementation of CLRRegEx on my site: [SQL Server 2005 and SQL Server 2008 Regular Expressions and pattern matching][1]

Using the sample implementation you can receive result by simple query using a simple regular expression: :(.+?):

SELECT [dbo].[fn_RegExMatch]('1:12.85:3:4', ':(.+?):', 1, 1)
[1]: http://www.pawlowski.cz/2010/09/sql-server-2005-and-sql-server-2008-regular-expressions-and-pattern-matching-2/
more ▼

answered Jul 21, 2011 at 12:13 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left

IN THE REQUIREMENT YOU MENTIONED THE CHAREACTER 1 IS AT TWO PLACES, IF THE CHARACTER 1 IS PRESENT AT THREE PLACES MEANS THEN HOW DO YOU WANT YOUR SUBSTRING, 1 -- SUBSTRING BETWEEN 1ST 1 AND 2ND 1 2 -- SUBSTRING BETWEEN 1ST 1 AND 3RD 1

LIKE ''1:12.81:3:4' THEN HOW DO YOU CONSIDER
more ▼

answered Jul 19, 2011 at 09:14 PM

Naveen Kumar gravatar image

Naveen Kumar
184 11 15 21

I think that's not the issue with this requirement.Is there any logic to get the mth & nth occurence of a character in a string by using a single select statement with out using other usd fun?.
Jul 19, 2011 at 09:21 PM nidheesh
check it out this one, select SUBSTRING('1:12.85:3:4',len('1:12.85:3:4')-charindex('1',reverse('1:12.85:3:4')),1)
Jul 20, 2011 at 12:50 AM Naveen Kumar

select SUBSTRING('1:12.85:3:4',len('1:12.85:3:4')-charindex('1',reverse('1:12.85:3:4')),1)

in the select if you want the occurances between ":" charachter just replace 1 with ":" in charindex function...

hope this helps you...
Jul 20, 2011 at 12:17 PM Naveen Kumar
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x12

asked: Jul 19, 2011 at 08:33 PM

Seen: 3541 times

Last Updated: Jul 21, 2011 at 02:19 PM