Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Delete all data after character

Posted on 2013-01-14
9
Medium Priority
?
373 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 66

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 93

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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 23

Accepted Solution

by:
Steve Wales earned 500 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
 
LVL 66

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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 300 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

886 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