Solved

SQL Delete all data after character

Posted on 2013-01-14
9
349 Views
Last Modified: 2013-01-14
I have a table that contains # signs and I need to delete all of the data BEFORE the # signs

Example Data:


qid	                qtext
1                      Weather#Sunny
2                      Weather#Rainy and cloudy
3                      Weather#Windy and foggy with possible rain

Open in new window

0
Comment
Question by:swaggrK
9 Comments
 

Author Comment

by:swaggrK
ID: 38775707
Also, data can appear like...

qid	                qtext
1                      Weather#Sunny
2                      Weather#Rainy and cloudy
3                      Weather#Windy and foggy with possible rain
4                      Report#Breezy and foggy with possible rain
5                      Weather Report#Snowy and partly cloudy
                                  

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38775712
Get the character index of the #, then take the left characters right before that index.

SELECT LEFT('Weather#Sunny', CHARINDEX('#', 'Weather#Sunny') - 1)

returns 'Weather'
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38775748
This is a twist, which returns the whole string if there is no # character:

SELECT LEFT(qtext, CHARINDEX('#', qtext + '#') - 1) AS Shorter
FROM SomeTable

Open in new window


As an update:

UPDATE SomeTable
SET qtext = LEFT(qtext, CHARINDEX('#', qtext + '#') - 1)

Open in new window

0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 125 total points
ID: 38775754
I think you're wanting the text AFTER the # sign, so if so:

select SUBSTRING(qtext, CHARINDEX('#', qtext,) + 1, LEN(qtext))

Open in new window


You could wrap an RTRIM() fuction around that as well to chop off any trailing spaces if it's an issue.

Pulling a substring for length greater than the total length of the string doesn't seem to bother SQL Server but if you wanted to work out the exact length by subtracting the start position of the # via another CHARINDEX call, that would be an option too.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 65

Expert Comment

by:Jim Horn
ID: 38775811
>think you're wanting the text AFTER the # sign
Doh.  Misread the question.

sjwales' comment is correct, although after a typo /  comma is deleted

 
Declare @qtext varchar(50) = 'Weather#Sunny'

select SUBSTRING(@qtext, CHARINDEX('#', @qtext) + 1, LEN(@qtext))

Open in new window

0
 

Author Comment

by:swaggrK
ID: 38775890
Yes, I want to KEEP the text AFTER the # sign.

So, my results would look like...

BEFORE:

qid	                qtext
1                      Weather#Sunny
2                      Weather#Rainy and cloudy
3                      Weather#Windy and foggy with possible rain
4                      Report#Breezy and foggy with possible rain
5                      Weather Report#Snowy and partly cloudy



AFTER:

qid	                qtext
1                      Sunny
2                      Rainy and cloudy
3                      Windy and foggy with possible rain
4                      Breezy and foggy with possible rain
5                      Snowy and partly cloudy

Open in new window

0
 

Author Comment

by:swaggrK
ID: 38775938
I am still not sure how to accomplish this based on the previous suggestions. Thnx.
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 75 total points
ID: 38775943
<point of order>
The title of this question is 'SQL Delete all data after character', but in the body it states the question is to delete all data before the # character.  Gotta be careful.
</point of order>

So, borrowing from the above, if you wish to permanently change the table so that the text before the # is deleted....

UPDATE YourTableName
SET qtext = SUBSTRING(qtext, CHARINDEX('#', qtext) + 1, LEN(qtext))
0
 

Author Comment

by:swaggrK
ID: 38775963
@jimhorn...thanks for the <point of order>

Also, this worked perfectly...THANKS!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now