break down string function - 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

Transact-SQL (2000)

break down string function

Author

Topic

Trent
Starting Member

39 Posts

Posted - 2005-02-10 : 09:31:10

Nothing too difficult, what I already have is below:

select min(GroupLabel) as Young, max(GroupLabel) as Old
from tStudyCategory
where StudyNo = '2004111740003A' and GroupKey LIKE 'A' + '%'

The Results:
Young | Old
-----------
25-34 | 65-100

What is the most simple way to get the lowest number in Young and the highest number in Old, something like below:
Young | Old
-----------
25 | 100

Thank you for any help!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-10 : 09:35:33

[code]
declare @low varchar(50), @high varchar(50)
set @low = '25-34'
set @high = '65-100'

select parsename(replace(@low, '-', '.'), 2), parsename(replace(@high, '-', '.'), 1)
[/code]

Go with the flow & have fun! Else fight the flow

Trent
Starting Member

39 Posts

Posted - 2005-02-10 : 09:59:47

RESOLVED
I knew it was something simple, thank you for pointing it out!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-11 : 00:11:43

quote:
Originally posted by spirit1

declare @low varchar(50), @high varchar(50)
set @low = '25-34'
set @high = '65-100'

select parsename(replace(@low, '-', '.'), 2), parsename(replace(@high, '-', '.'), 1)

Go with the flow & have fun! Else fight the flow

If old is 65-10, yours gives 10

I think this work for all combinations

declare @low varchar(50), @high varchar(50)
set @low = '25-34'
set @high = '65-100'

if convert(numeric,left(@low,charindex('-',@low)-1))<convert(numeric,right(@low,len(@low)-charindex('-',@low)))
select convert(numeric,left(@low,charindex('-',@low)-1))
else
select convert(numeric,right(@low,len(@low)-charindex('-',@low)))

if convert(numeric,left(@high,charindex('-',@high)-1))>convert(numeric,right(@high,len(@high)-charindex('-',@high)))
select convert(numeric,left(@high,charindex('-',@high)-1))
else
select convert(numeric,right(@high,len(@high)-charindex('-',@high)))

Madhivanan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources