Help with Case statement - 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 2005 Forums

Transact-SQL (2005)

Help with Case statement

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-10-05 : 06:47:29

Hi I'm trying to do the following Case:

SELECT
CASE SUBSTRING(prsheet, LEN(prsheet)-2,1)
WHEN '-' THEN
LEFT(prsheet,15) + '%' --this is where i am having the issue--
ELSE
prsheet
END AS PriceSheet

FROM coPriceSheet
WHERE prsheet = 'UK-P032-9899800-00'

in this instance where the 3rd last char is '-' i want to select everything to the left of this '-' i.e. 'UK-P032-9899800'. But because this field can vary in Length is there anyway i can select everything to the LEFT with specifying how many chars??

Sachin.Nand

2937 Posts

Posted - 2010-10-05 : 07:09:45

[code]
declare @str varchar(40)='UK-P032-9899800-00'
select @str

select
case substring(@str, len(@str)-2,1)
when '-' then replace(@str,(substring(@str, len(@str)-2,len(@str))),'') end
[/code]

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-05 : 07:19:35

declare @str varchar(40)
set @str='UK-P032-9899800-00'

select left(@str,len(@str)-charindex('-',reverse(@str)))

Madhivanan

Failing to plan is Planning to fail

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-10-05 : 07:26:40

Brilliant both scenarios work! A big thank you to both you guys

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-05 : 10:00:12