SQL Delete all data after character

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

swaggrKAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
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
 
swaggrKAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Patrick MatthewsCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
swaggrKAuthor Commented:
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
 
swaggrKAuthor Commented:
I am still not sure how to accomplish this based on the previous suggestions. Thnx.
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
<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
 
swaggrKAuthor Commented:
@jimhorn...thanks for the <point of order>

Also, this worked perfectly...THANKS!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.