Regular Expression pattern required

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Regular Expression pattern required

Author

Topic

pareekfranksoul
Starting Member

26 Posts

Posted - 2009-08-05 : 02:42:25

Need regular expression pattern to remove n occurrences of "-" with one and remove "-" from starting and last position of string.

Input String:"-----p-a----n-k-a-j-1-5-4-6-3-P-A-N-K-A-J- ";

Required Output:"p-a-n-k-a-j-1-5-4-6-3-P-A-N-K-A-J ";

Thanks
Pankaj

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-05 : 03:21:22

[code]DECLARE @Sample VARCHAR(100)

SET @Sample = '-----p-a----n-k-a-j-1-5-4-6-3-P-A-N-K-A-J- '

SELECT SUBSTRING(REPLACE(REPLACE(@Sample, '--', '-'), '- ', ' '), PATINDEX('%[^-]%', REPLACE(@Sample, '--', '-')), LEN(@Sample))[/code]

N 56°04'39.26"
E 12°55'05.63"

pareekfranksoul
Starting Member

26 Posts

Posted - 2009-08-05 : 05:13:40

I have done it through regular expression like this:

result = System.Text.RegularExpressions.Regex.Replace(result, "(-*-)", "-");
result = System.Text.RegularExpressions.Regex.Replace(result, "^-", "");
result = System.Text.RegularExpressions.Regex.Replace(result, "-$", "");

I think It will take less overhead then above.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-05 : 05:18:55

Oh, I didn't see this was an ASP.net forum.

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-05 : 05:20:35

[code]select data,
replace(replace(replace(data,'-','-~!#$^'),'~!#$^-',''),'~!#$^','') as new_data
from
(
select '-----p-a---------------------------------n-k-a-j-1-5-4-6-3-P-A-N-K-A-J- ' as data union all
select '-----p-a----n-k-a-j-1-5-4-6-3-P-A-N-K-A-J- ' union all
select '-----p-a-----n-k-a-------j-1-5-4-6-3-----------P-A-N-K-A-J- '

)
as t[/code]
from http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/06/squeeze-function.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources