ignoring characters in field - 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)

ignoring characters in field

Author

Topic

kpearson
Starting Member

3 Posts

Posted - 2005-06-30 : 15:23:25

I am new to SQL and I have the following problem:
I have a field named addsuf with data like,
#1
M1
APTB
UNITA
STE100
I only want to recognize the 1, B, 100, etc. and not the M, APT, UNIT, etc. The user will be entering a number or letter into the textbox. Is there a way to do this? Part of my statement that I have so far goes like this

addsuf = '" & txtaddsuf.Text & "'

Any help would be greatly appreciated,
kpearson

kpearson
Starting Member

3 Posts

Posted - 2005-06-30 : 16:03:42

I got it working using a LIKE statement, but now if nothing is entered in the textbox my result includes all the records. Anyway around this?
Thanks,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-01 : 01:17:08

You can check for emty value
If ltrim(rtrim(varvalue))<>''
--Your Like query

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-07-04 : 22:21:04

post your query
else
you can add a condition in your where clause e.g.
where field1 like coalesce('%' + @var1 + '%',field1)

but this will give you table scanning

--------------------
keeping it simple...

kpearson
Starting Member

3 Posts

Posted - 2005-07-05 : 14:40:17

I am still confused. Are you saying the statement would read:

addsuf LIKE COALESCE('%" & txtaddsuf.Text & "%', "")

I of course get an error with this.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-06 : 01:38:01

I think you need to check for empty value in your Front end application as specified in my previous reply

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources