Remove all after space in SQL Server data column

Question

I have a data column in SQL Server that always starts with a number and sometimes has a space after the number with additional details. This is a text column. I need a query that will remove everything after and including space if there is a space or additional info. In other words, I need only the number to be in this column. Sometimes there is only a number and sometimes not. Can I get assistance in a query that will do this?

This sounds fairly easy to do. Please update your question with what you've tried and why it didn't work. — Kermit, May 22 '13 at 17:55
Post what you have tried and what issues you had and I'm sure we can help — Kevin Kunderman, May 22 '13 at 17:56
just do each step, find a space if no space take whole value. if space take everything to the left of the position of the space. agree w/ freshprince... pretty straightforward. — xQbert, May 22 '13 at 18:00
@trevoray The reason for the downvotes is because you pretty much asked "write my code for me". That isn't how this site works. You get stuck with some code that doesn't do what you think it should, you post a question, we help explain why it doesn't do it and then provide a possible solution. — Steve's a D, May 22 '13 at 19:07

score 2 · Answer 1 · answered May 22 '13 at 18:01

2

Try this

 Select case When charIndex(' ', columnName) > 0 
           Then substring(columnName, 1, charIndex(' ', columnName)-1)
           else ColumnBame End
 From tableName

answered May 22 '13 at 18:01

Charles Bretana

143,358
22
150
216

xShirase · Accepted Answer · 2013-05-22T18:17:33.553

2

Try this one (replacing mydb and textfield with whatever name you have) :

UPDATE mytable
SET textfield = LEFT(textfield+' ', CHARINDEX(' ', textfield) - 1)
WHERE CHARINDEX(' ', textfield) > 0

edited May 22 '13 at 18:17

answered May 22 '13 at 18:05

xShirase

11,975
4
53
85

Awesome! Thanks XShirase! And thanks for not requiring me to post what I've tried and what didn't worked. Your answer works perfectly which is exactly what I asked for! – trevoray May 22 '13 at 18:36
You should still have posted what you had tried. SO is about sharing knowledge, not getting easy answers to your problems. By researching, you'd have found the answer, here : http://stackoverflow.com/questions/1668014/sql-server-replace-remove-all-after-certain-character?rq=1 You're forgiven, this time, if you promise to read the FAQ ^^ – xShirase May 22 '13 at 18:48
1

This solution will PERMANENTLY eliminate the extra data... Are you sure that's what you want? If it is, you might consider modifying client code so that these unwanted details are not inserted in the first place (Make the column a numeric type rather than textual etc.). Otoh, if you just want to eliminate the details when you read (select) the data (as your question suggests), then you should use a Select statement, not an Update. – Charles Bretana May 22 '13 at 20:11

score 0 · Answer 3 · answered May 22 '13 at 18:00

I would suggest something like this:

SELECT CASE 
WHEN ISNUMERIC(SUBSTRING(YourNumber, 1)) THEN SUBSTRING(YourNumber, 1, len(YourNumber) -1)
WHEN ISNUMERIC(SUBSTRING(YourNumber, 2)) THEN SUBSTRING(YourNumber, 2, len(YourNumber) -2)
WHEN ISNUMERIC(SUBSTRING(YourNumber, 3)) THEN SUBSTRING(YourNumber, 3, len(YourNumber) -3)
WHEN ISNUMERIC(SUBSTRING(YourNumber, 4)) THEN SUBSTRING(YourNumber, 4, len(YourNumber) -4)
END
FROM TableName

Remove all after space in SQL Server data column

3 Answers3