SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Delete text in nvarchar row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kieran5405
Yak Posting Veteran

Ireland
94 Posts

Posted - 10/09/2012 :  14:32:45  Show Profile  Reply with Quote

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
2869 Posts

Posted - 10/09/2012 :  14:42:49  Show Profile  Reply with Quote
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
Go to Top of Page

kieran5405
Yak Posting Veteran

Ireland
94 Posts

Posted - 10/09/2012 :  14:54:46  Show Profile  Reply with Quote
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

Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 10/09/2012 :  15:21:54  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/09/2012 :  23:24:07  Show Profile  Reply with Quote
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/

Go to Top of Page

kieran5405
Yak Posting Veteran

Ireland
94 Posts

Posted - 10/10/2012 :  05:43:13  Show Profile  Reply with Quote

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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/11/2012 :  00:19:51  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000