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

avatar image

nidheesh
90 13 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

avatar image

Fatherjack ♦♦
43.7k 79 98 117

(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

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)

more ▼

answered Jul 21, 2011 at 12:13 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(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

avatar image

Naveen Kumar
244 18 22 28

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.

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:

x1069
x20

asked: Jul 19, 2011 at 08:33 PM

Seen: 4821 times

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

Copyright 2016 Redgate Software. Privacy Policy