| Author |
 Topic  |
|
|
kieran5405
Yak Posting Veteran
Ireland
92 Posts |
 Posted - 10/09/2012 : 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
Flowing Fount of Yak Knowledge
USA
2866 Posts |
Posted - 10/09/2012 : 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
Ireland
92 Posts |
Posted - 10/09/2012 : 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
Flowing Fount of Yak Knowledge
USA
2866 Posts |
Posted - 10/09/2012 : 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
India
47040 Posts |
Posted - 10/09/2012 : 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
Ireland
92 Posts |
Posted - 10/10/2012 : 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
India
47040 Posts |
Posted - 10/11/2012 : 00:19:51
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Delete text in nvarchar row
