Delete text in nvarchar row - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Delete text in nvarchar row

Author

Topic

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2012-10-09 : 14:32:45

Hi,

I have an nvarchar column in a sql 2008 DB called 'description' and I have some text in the column which I want to keep...but also other text I want to delete. The text I want to delete is in an open and close square bracket i.e. [ <text> ]. The problem I have is that the delete text is different in each row (bar it being in the square brackets).
Any advice how to do this?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-10-09 : 14:42:49

I'm not exactly sure what you're looking for, but this should get you started in the right direction.

DECLARE @text nvarchar(max) =' ajkahdk [jdajlk] 38q24j'
SELECT STUFF(@text,CHARINDEX('[',@Text),LEN(@text) - CHARINDEX(']',@Text)+1,'')

Jim

Everyday I learn something that somebody else already knew

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2012-10-09 : 14:54:46

Thanks for replying!

Your code works great but when i have an space in the open/close brackets as below - it keeps the close bracket i.e. it returns - 'ajkahdk ] 38q24j'

I have tried messing with it but im missing something.

DECLARE @text nvarchar(max) =' ajkahdk [ jdajlk ] 38q24j'
SELECT STUFF(@text,CHARINDEX('[',@Text),LEN(@text) - CHARINDEX(']',@Text)+1,'')

quote:
Originally posted by jimf

I'm not exactly sure what you're looking for, but this should get you started in the right direction.

DECLARE @text nvarchar(max) =' ajkahdk [jdajlk] 38q24j'
SELECT STUFF(@text,CHARINDEX('[',@Text),LEN(@text) - CHARINDEX(']',@Text)+1,'')

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-10-09 : 15:21:54

I'm missing something something as well. Whenever you add a space in between the brackets, you have to increment this part by 1 CHARINDEX(']',@Text)+1. So one space +2, 3 spaces +4, etc. I have a horrible solution, but want to try to find the right one.

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-09 : 23:24:07

slight tweaking and it will work regardless of any number of spaces inside []


DECLARE @text nvarchar(max) =' ajkahdk [ jdajlk                  ] 38q24j'
SELECT STUFF(@text,CHARINDEX('[',@Text), CHARINDEX(']',@Text)-CHARINDEX('[',@Text)+1,'')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2012-10-10 : 05:43:13

thanks that got it working...much appreciated to both jimf and visakh16!!

quote:
Originally posted by visakh16

slight tweaking and it will work regardless of any number of spaces inside []

DECLARE @text nvarchar(max) =' ajkahdk [ jdajlk ] 38q24j'
SELECT STUFF(@text,CHARINDEX('[',@Text), CHARINDEX(']',@Text)-CHARINDEX('[',@Text)+1,'')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-11 : 00:19:51

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources