Select parts of information - 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

General SQL Server Forums

New to SQL Server Programming

Select parts of information

Author

Topic

1821
Starting Member

18 Posts

Posted - 2008-04-20 : 17:05:52

Hey guys,

I was wondering if this is possible I have to be able to just select the number before the '-' in acolumn which is formatted like 123-456 so I just need the the 123 part.

Any ideas how this can be achieved?

Thanks.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-20 : 18:27:37

select left(ColumnName, charindex('-', ColumnName)-1) from TableName

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-21 : 00:13:55

Make sure you've a '-' always appearing in the string before applying charindex else it will break

select left(ColumnName, case when charindex('-', ColumnName)>0 then charindex('-', ColumnName) else len(ColumnName)+1 end-1) from TableName

raky
Aged Yak Warrior

767 Posts

Posted - 2008-04-21 : 00:49:39

select
case when charindex('-', ColumnName) > 0 then substring(ColumnName, 1,charindex('-', ColumnName)-1)
else Null end as string
from TableName

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-21 : 04:36:08

Are you sure that works, raky?

1821: If there is a possibility '-' is not there, you need to decide on what you want to return - e.g. null or the whole thing. I would do it like this...

To return null...

select left(ColumnName, nullif(charindex('-', ColumnName), 0)-1) from TableName

To return the whole thing...

select left(ColumnName, nullif(charindex('-', ColumnName + '-'), 0)-1) from TableName

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-21 : 05:19:50

If you want to show data in front end application, use split function there

Madhivanan

Failing to plan is Planning to fail

1821
Starting Member

18 Posts

Posted - 2008-04-26 : 20:22:39

Thanks for all the replies they all helped.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources