Replace a String within a String

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

General SQL Server Forums

New to SQL Server Programming

Replace a String within a String

Author

Topic

annad
Starting Member

1 Post

Posted - 2012-09-11 : 06:30:34

Hi there,

I am need to replace within a character string the characters "2012-" and all the preceeding chracters until the space character, with a space

e.g

ORIGINAL STRING :
"SSSSSS2012-08-2007:39001 KKKKKKK2012-08-20NNN asdad\sdasdasd"

REPLACED STRING :
"SSSSSS KKKKKKK asdad\sdasdasd"

Could anybody please help!?

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-11 : 07:37:35

The code shown below requires a string splitter function that you can copy from Fig. 21 in Jeff Moden's blog here: http://www.sqlservercentral.com/articles/Tally+Table/72993/ Copy the function and run it to install it. Then use the code as shown below.

declare @x varchar(255) = 'SSSSSS2012-08-2007:39001 KKKKKKK2012-08-20NNN asdad\sdasdasd'
SELECT
	Item + ' '  AS [text()]
FROM
(
	SELECT
		dsk.ItemNumber, 
		dsk.Item
	FROM
		dbo.DelimitedSplit8K(REPLACE(@x,'2012',' '),' ') dsk
) s
WHERE s.ItemNumber%2 = 1
FOR XML PATH('');

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-09-11 : 08:55:35

off topic but.

Jeff -- if you check this:

Is there another location for those docs that you don't have to register for. A blog or similar.

I always feel a little dirty posting links to things that people have to sign up to see.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-12 : 03:20:51

Hi annad,

DECLARE @x varchar(255) = 'SSSSSS2012-08-2007:39001 KKKKKKK2012-08-20NNN asdad\sdasdasd'
DECLARE @pos int = charindex('2012-', @x, 1 ), @pos_space int = CHARINDEX(' ', @x, 1 ), @start int = 1

WHILE (@pos != 0 )
BEGIN
SET @x = replace (@x, right(substring(@x, 1 , @pos_space),len( substring(@x, 1 , @pos_space - @pos))+1), ' ')
SET @pos = charindex('2012-', @x, 1 )
SET @pos_space = charindex(' ', @x, @pos )
END
PRINT @x

--
Chandu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-13 : 02:37:36

quote:
Originally posted by Transact Charlie

off topic but.

Jeff -- if you check this:

Is there another location for those docs that you don't have to register for. A blog or similar.

I always feel a little dirty posting links to things that people have to sign up to see.

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/

Yes SSC always does it. But Jeff Moden does not have his own blog outside of SSC

Madhivanan

Failing to plan is Planning to fail

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-13 : 07:07:35

I rationalize it by saying to myself that SSC's registration is free, and if you are going to be doing a lot of stuff with SQL, being registered there is not a bad idea anyway.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-09-13 : 11:56:17

yeah -- I don't have a problem with the site itself -- just the big pop up "YOU MUST SIGN IN TO SEE THIS STUFF" poput that comes up every time a new person visits it.

I'd imagine that most people (myself included) are immediately turned off by that.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources