trimming everything after '_' - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

trimming everything after '_'

Author

Topic

patman
Starting Member

6 Posts

Posted - 2006-07-31 : 10:05:34

hi all

I have a column in a table where a typical entry is 123456AB_987654.

How do I do a SELECT so that all values are trimmed down to just the value up to the '_', i.e. I want to be left with '123456AB'. I looked up the trim function, but could only find how to eliminate one type of charater, e.g. the 0's or white spaces.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-31 : 10:07:04

[code]declare @str varchar(20)

select @str = '123456AB_987654'

select left(@str, charindex('_', @str) - 1)[/code]

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-31 : 10:09:06

declare @s varchar(100)
set @s='123456AB_987654'
select SUBSTRING(@s,1,charindex('_',@s)-1) as col1

Srinika

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-31 : 10:11:48

patman
Starting Member

6 Posts

Posted - 2006-07-31 : 10:16:26

thanks guys thats great but how do iincorporate this into a SELECT statement so that it returns all entries in the column with the values trimmed down.

From your solution, it looks like this will trim individual entries down.

thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-31 : 10:29:59

quote:
Originally posted by patman

From your solution, it looks like this will trim individual entries down.

patman
In ur question was there a table name or column name ?

Just think and reach the solution. U r too close to the solution

Srinika

patman
Starting Member

6 Posts

Posted - 2006-07-31 : 10:33:22

quote:
Originally posted by Srinika

quote:
Originally posted by patman

From your solution, it looks like this will trim individual entries down.

patman
In ur question was there a table name or column name ?

Just think and reach the solution. U r too close to the solution

Srinika

oh yeah, i got it now srinika, thanks for your help.

One more question though, I have just noticed that not all of the ntries have a '_' in them. I though that the trim would ignore any entries that didnt, and just list the actual value, but instead it returns NULL for these, eg. 123456 will be returned as NULL, as there is no '_' in it. How can i get round this please?

Kristen
Test

22859 Posts

Posted - 2006-07-31 : 10:38:37

select COALESCE(SUBSTRING(@s,1,charindex('_',@s)-1), @s) as col1

Kristen

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-31 : 10:39:28

I think
U can make use of ISNULL function

edit :
kristen's solution is much better