Select Query- Trim functions - SQL SERVER 2008

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

Select Query- Trim functions - SQL SERVER 2008

Author

Topic

paramu
Posting Yak Master

151 Posts

Posted - 2009-10-12 : 10:45:28

I want to have a select query to select an item with trim functions to remove the leading & trailing spaces from that field value.

My Query

Select q.sup_code,p.name,p.email from qmst q,pmst p where q.mpr_no='21' and p.code=q.sup_code"

Actually the value available in q.mpr_no=' 21 '

So how to remove the spaces in both sides to q.mpr_no ?

Thanks

Paramu @ PARANTHAMAN

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-12 : 10:51:15

where ltrim(rtrim(q.mpr_no))='21'

No, you're never too old to Yak'n'Roll if you're too young to die.

paramu
Posting Yak Master

151 Posts

Posted - 2009-10-12 : 10:57:52

Please check it's not giving the result

Thanks

Paramu @ PARANTHAMAN

paramu
Posting Yak Master

151 Posts

Posted - 2009-10-12 : 11:01:34

Is there any possibility to check as integer like

where int(q.mpr_no)='21'

Thanks

Paramu @ PARANTHAMAN

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-12 : 11:14:44

where convert(int,q.mpr_no)=21

No, you're never too old to Yak'n'Roll if you're too young to die.

paramu
Posting Yak Master

151 Posts

Posted - 2009-10-12 : 11:37:05

Thanks, But its also failed, since there may be some other charectars which is look like a blank, So how to solve it?

I tried by Substring(q.mpr_no,3,2)='21' working fine.

But I cant identify the 1st 2 charactars. Any Other Ideas?

Thanks

Paramu @ PARANTHAMAN

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-12 : 11:57:39

Maybe there are some special characters in your column?

See here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=119083

No, you're never too old to Yak'n'Roll if you're too young to die.

paramu
Posting Yak Master

151 Posts

Posted - 2009-10-13 : 01:30:02

Thankyou, I came to know many ways from your guidences.

Thanks

Paramu @ PARANTHAMAN

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-13 : 02:41:02