Remove text from a string by pattern matching?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2012 Forums

Transact-SQL (2012)

Remove text from a string by pattern matching??

Author

Topic

nandersen
Starting Member

2 Posts

Posted - 2014-05-23 : 15:44:16

Hello,

I have a column that contains data in this pattern:

TEXT1 | GUID1 ; TEXT2 | GUID2 ;

I would like to query the data and return it in this pattern:

TEXT1; TEXT2;

I am looking for suggestions on how I would write my query to remove everything between the | and ; characters. One more wrinkle - I do not know how many occurrences of GUIDS there will be in a particular row of data - currently there are anywhere between 0 and 24 but this could increase in the future.

All help appreciated. Thanks in advance!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-23 : 16:11:46

Here is an example:

DECLARE @x VARCHAR(255) = 'TEXT1|GUID1;TEXT2|GUID2;'
SELECT Item + ';'
FROM dbo.DelimitedSplit8K(REPLACE(@x,';','|'),'|')
WHERE ItemNumber%2 = 1 AND Item <> ''
FOR XML PATH('');

The string splitter function dbo.DelimitedSplit8K is available on this page: http://www.sqlservercentral.com/articles/Tally+Table/72993/

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-24 : 00:52:33

The is the simple way to achieve it without using split function


DECLARE @Var varchar(MAX)='TEXT1 | GUID1 ; TEXT2 | GUID2 ; TEXT3 | GUID3; TEXT4 | GUID4'

WHILE   PATINDEX('%|%',@Var)<>0
BEGIN
	DECLARE @t varchar(MAX)=(SELECT SUBSTRING(@Var,PATINDEX('%|%',@Var),7))
	SET @Var= (SELECT REPLACE (@Var,@t,''))
END
	SELECT @Var

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-24 : 01:53:37

Just noticed that Input may vary so following should work ...


DECLARE @Var varchar(100) ='TEXT1 | GUID178877 ; TEXT2 | GUID888882 ; TEXT3 | GUID3878 ; TEXT4 | GUID768778784 '

SELECT * INTO #temp FROM (
SELECT a.x.value('.','VARCHAR(50)') col FROM 
(SELECT CAST('<r>'+REPLACE(@Var,';','</r><r>')+'</r>' AS XML)y ) b
CROSS APPLY b.y.nodes('r') AS a(x)
)a
SELECT STUFF((SELECT ';'+ LEFT(col,CHARINDEX('|',col)-1)FROM #temp FOR XML PATH ('')),1,1,'')

DROP TABLE #temp

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

nandersen
Starting Member

2 Posts

Posted - 2014-05-27 : 11:28:41

Thank you guys for the responses, I will try them out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources