How can I change the results of a field in a SQL query to only include characters up to the first underscore?

Question

I am using SQL Server 2008 R2

I have a table ITEM:

NO_ITEM    LABEL 
121_54_7   aaaaaa
32_5       jjjjjj
6          88888
9987_54_4  oooooo

What I want:

NO_ITEM    LABEL 
121        aaaaaa
32         jjjjjj
6          88888
9987       oooooo

Just select the first data by omitting the rest after _.

http://stackoverflow.com/questions/8299176/how-to-select-only-the-characters-appearing-before-a-specific-symbol-in-a-sql-se — mjroodt, Aug 29 '13 at 15:11

score 3 · Accepted Answer · answered Aug 29 '13 at 15:12

3

Sure, you could do something like this:

SELECT SUBSTRING(NO_ITEM, 1, CHARINDEX('_', NO_ITEM + '_')) AS NO_ITEM,
    LABEL,
FROM table

answered Aug 29 '13 at 15:12

Mike Perrenoud

2

You need to pad; `SUBSTRING(NO_ITEM, 0, CHARINDEX('_', NO_ITEM + '_'))` for cases when there is no `_` – Alex K. Aug 29 '13 at 15:13
@AlexK., fantastic catch. Thanks a lot! – Mike Perrenoud Aug 29 '13 at 15:14

1 Answers1