I need your help real quick.... I use SQL 2008 R2
I have this column that has this kind of data
1_kka
2_bge
3_kil
the column should have only the number which is
1
2
3
So how should i replace it by using replace and substring?
Thank you!!
1
-
1There is no SQL Server 2012 R2... it's either 2008R2 or 2012? :)Nenad Zivkovic– Nenad Zivkovic2013-07-19 18:12:02 +00:00Commented Jul 19, 2013 at 18:12
Add a comment
|
5 Answers
In MySQL, you can do this remarkably easily by just adding 0 tot he string:
select col + 0
from t
The + 0 treats the string as an integer, and it automatically converts any numbers at the beginning of the string into a number.
In other databases, something like this will work:
select cast(left(col, 1) as int)
from t
Assuming that the initial number has exactly one digit (as in the examples in your question).
1 Comment
Ban
Sorry I use SQL 2012 R2
If you're using SQL Server and if number is not limited to 1 digit you can use
SELECT SUBSTRING(column, 1, CHARINDEX('_', column)-1)
FROM Table
answered Jul 19, 2013 at 17:42
Comfortably Numb
39.9k17 gold badges82 silver badges147 bronze badges
Comments
If multiple digits can appear before the underscore, you'll need something like this:
SELECT LEFT(Fieldname,CHARINDEX('_',Fieldname)-1)
FROM table
Comments
This will update all values, in every row that contains your underscore.
Update TableName
Set FieldName = Substring(FieldName, 1, CharIndex('_', FieldName) - 1)
Where CharIndex('_', FieldName) >= 1
Comments
try this:
update <TABLE> set COL = LEFT(COL,CHARINDEX('_', COL)-1)
