Trouble with RIGHT 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

General SQL Server Forums

New to SQL Server Programming

Trouble with RIGHT Function

Author

Topic

mpolatchek
Starting Member

2 Posts

Posted - 2010-12-02 : 14:58:01

Here is my syntax for select all valies in a column that end with '01'
--refresh table from source
Truncate table #tmp_calldetail
insert into #tmp_calldetail
select Eventdate, upper(substring(ExtnName, 4,3)), Starttime, CalledFrom, callduration, CallId from DExtnCallDetail
order by Eventdate, substring(ExtnName, 4,3)

select * from #tmp_calldetail where right(rtrim(ltrim("TASK_calledfrom")),2) = '01'

My table contains a number of values in column TASK_calledfrom which do match the criteria.

Any ideas

Thanks,

Melvyn Polatchek

Melvyn Polatchek

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-02 : 15:29:48

change
right(rtrim(ltrim("TASK_calledfrom")),2) = '01'
to
right(rtrim(ltrim(TASK_calledfrom)),2) = '01'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2010-12-02 : 15:44:44

This may work:

select * from #tmp_calldetail
where reverse(substring(reverse(ltrim(rtrim("TASK_calledfrom"))),1,2)) = '01'

Some days you're the dog, and some days you're the fire hydrant.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-07 : 03:07:54

quote:
Originally posted by Skorch

This may work:

select * from #tmp_calldetail
where reverse(substring(reverse(ltrim(rtrim("TASK_calledfrom"))),1,2)) = '01'

Some days you're the dog, and some days you're the fire hydrant.

It may not. Becuase TASK_calledfrom is a column name and not a literal value

Madhivanan

Failing to plan is Planning to fail

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-07 : 04:26:59

quote:
Originally posted by madhivanan

quote:
Originally posted by Skorch

This may work:

select * from #tmp_calldetail
where reverse(substring(reverse(ltrim(rtrim("TASK_calledfrom"))),1,2)) = '01'

Some days you're the dog, and some days you're the fire hydrant.

It may not. Becuase TASK_calledfrom is a column name and not a literal value

Madhivanan

Failing to plan is Planning to fail

Think that's already been pointed out

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mpolatchek
Starting Member

2 Posts

Posted - 2010-12-07 : 11:03:14

Hi Madhivanan

You hit the nail on the head. It was my eventual solution. Actually the reason the 'right' function did not work was. I figured out later, the apparent blanks characters I was attempting to trim were actually nulls which would not trim, so 'right' would fail no matter what The orignal data came from a machine flat file which somehow generated nulls.

Thanks for your interest,

Melvyn Polatchek

Melvyn Polatchek

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources