Removing unwanted space in the result of SELECT query

Posted by Sankar20092010 under VB.NET on 11/2/2012 | Points: 10 | Views : 993 | Status : [Member] | Replies : 3
Hi....

In my table there is a row which contains data imported from a file. In tha table in a column after the name extra unwanted space also accidently included.
The problem is while executing the query having WHERE condition i am not geting the result.

For eg Select name from [table] where name = 'name' since name has many spaces trailing it will not give the result . To get the result i have to insert correct extra spaces .

In .NET on using Trim function in the executereader this is not working.

Any good solution please reply as soon as possible.

Thanks and Regards

Sankar




Responses

Posted by: Nkkppp on: 11/4/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,

Why don't use " like" operator

Select name from [table] where name like '%name%' (OR)



Regards,
Prathap.



Sankar20092010, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Deepraj on: 11/5/2012 [Member] Starter | Points: 25

Up
0
Down
Hi shankar,

I think it is because of the datatype size, use varchar instead of char, or modify the size as enough as you need.


Sankar20092010, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Seng2hs on: 11/5/2012 [Member] Starter | Points: 25

Up
0
Down
CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@string))
END
GO

********************************************************************************
Use this function in your where condtion
Select name from [table] where dbo.TRIM(name) = 'name'

Hope this helps you

Sankar20092010, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response