Regular Expression Replace using sql server function

Question

Regular Expression Replace using sql server function

rohit.kumar.barik

Say Hey Kid

Points: 672
More actions
August 29, 2011 at 11:16 am

#247098

Hi All
I have following Values.
ABC EN 709+A1
ABC-EN 1/A1
ABC 104233/1M
ABC-EN ISO 50065-4-7 CORR
ABC-EN ISO 832/AC
ABC 207002 IN
ABC-EN ISO 100171 ERTA
After using RegexReplace output should be
ABC EN 709+A1 [it will remain as it is]
ABC-EN 1
ABC 104233
ABC-EN ISO 50065-4-7
ABC-EN ISO 832
ABC 207002
ABC-EN ISO 100171
We have to write one function.
Thanks in Advance
Rohit

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Homework?

It is totally unclear from your post what you are trying to do. Clarify what you want to accomplish and post what you have tried.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

R.P.Rozema SSChampion Points: 12317 More actions · Answer 2

declare @tbl table (

txt varchar(max) not null

);

insert @tbl(txt)

select 'ABC EN 709+A1'

union all select 'ABC-EN 1/A1'

union all select 'ABC 104233/1M'

union all select 'ABC-EN ISO 50065-4-7 CORR'

union all select 'ABC-EN ISO 832/AC'

union all select 'ABC 207002 IN'

union all select 'ABC-EN ISO 100171 ERTA';

with cte as (

select row_number() over (order by (select 1)) as nr, t.txt

from @tbl t

)

select case patterns.mpos when 0 then t.txt

else substring(t.txt, 1, isnull(patterns.mpos - 1, datalength(t.txt)))

end

from cte t

outer apply (

select min(patindex(pattern.pat, txt.txt)) as mpos

from (

select t.txt

) txt

cross join (

select '%/%' as pat

union all select '% IN'

union all select '% ERTA'

union all select '% CORR'

) pattern

where patindex(pattern.pat, txt.txt) > 0

) patterns;

Good luck explaining this to your teacher 😉

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Lowell SSC Guru Points: 323518 More actions · Answer 3

R.P.Rozema (8/29/2011)
Good luck explaining this to your teacher 😉

🙂

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!