Partial of Where Clause -- resolved

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 2008 Forums

Transact-SQL (2008)

Partial of Where Clause -- resolved

Author

Topic

tiger6200
Starting Member

3 Posts

Posted - 2010-06-02 : 14:28:06

I am not sure how to phrase this, but what I am trying to do is the following SQL:

Select cardid from tablename
where cardid[7,9] = "123"

In other words, I am trying to select only records that the cardid,
from position 7 to 9, contains "123".

I can do:
select substring(cardid,7,3) from tablename to display these positions, but I can't use the substring after/on the WHERE clause.

Any helps is greatly appreciated!

Peter

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-02 : 14:30:52

quote:
but I can't use the substring after/on the WHERE clause

Sure you can.

Are you saying this doesn't work?

Select cardid from tablename
where substring(cardid,7,3) = "123"

tiger6200
Starting Member

3 Posts

Posted - 2010-06-02 : 14:43:14

Thanks for the quick reply. No, that does not work. When I tried that it game me the follow errors:
Msg 207, Level 16, State 1, Line 2
Invalid column name '123'.

That is why I am confused. I know in Informix database, you use the
following:
select cardid[7,9] from tablename
where cardid[7,9] = "123";

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-02 : 14:51:54

Change double quotes to single quotes

Select cardid from tablename
where substring(cardid,7,3) = '123'

tiger6200
Starting Member

3 Posts

Posted - 2010-06-02 : 14:57:33

Okay, that works. Never thought MS is that picky or stupid in my opinion. Users should be able to enclose text with either or.

Thank you very much and really appreciate your quick response.

Highest regards,

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-02 : 15:10:35

Np. Welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources