How to ignore " " (space) in a value

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

How to ignore " " (space) in a value

Author

Topic

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-11-01 : 06:56:46

Team
I have a web application setup (using Iron Speed Designer) which helps analysts add records into an SQL Table "requests".

I have a column as request_number (this is the primary key), data type is nvarchar(13).
Example request number is REQ123456-001
Now when an agent enters a number as 'REQ123456 ' **Note there is a space. so when i export the data and do a vlookup for any xyz reason, i dont get the correct updates for 'REQ123456 ' since 'REQ123456 ' <> 'REQ123456'
i cannot reduce the number of characters to nvarchar(9) as i may have some data as REQ123456-001, which has a character count > 9.
Some times i have an analyst updating the request number as 'REQ 123456'

My query is, how do i avoid the " " (space) which gets added, can SQL ignore this, before it gets recorded?

Any help on this is appreciated.

Ewan Gilby

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-01 : 07:24:11

Yes, While inserting the data in table from sql procedures you can use trim function

RTRIM(<string>) or LTRIM(<string>) or LTRIM(RTRIM(<string>)

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

shaggy
Posting Yak Master

248 Posts

Posted - 2010-11-01 : 07:25:38

try this
declare @t table (a varchar(10))

insert @t select 'REQ123456 '
union select 'RE Q123456'
union select 'R E Q12345'
select Case when right(a,1)= ' ' then left(a,datalength(a)-1) else a end ,* from @t

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-11-01 : 07:52:12

Thanks for you suggestions. It helps.

But how to i remove a space if between the request number. like 'REQ 123456'

Ewan Gilby

shaggy
Posting Yak Master

248 Posts

Posted - 2010-11-01 : 08:01:29

select REPLACE('REQ 123456',' ','')

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-01 : 08:04:10

Try this -
SELECT REPLACE('REQ 123456', ' ','' )

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-11-01 : 08:13:48

Thanks for your prompt responses.

instead of LTRIM, RTRIM i have used REPLACE as

REPLACE(@p_request,' ','') (Where @p_request is the variable which will be assigned the request number)

in my SQL Procedure,
this seems to work, however would like to know if this could turn out to be a disadvantage in any way,is there something that i am missing to include in the SQL statement, is this the best way to tackle the space issue?
Require your expert openion on this. Thanks

Ewan Gilby

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-01 : 08:20:47

I believe that these validations should be done at front end.
You can ask your front end developer to not allow user to enter space then you will always get correct data.
If this does not affect Business rules

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-11-01 : 09:31:42

Thanks you all for your help with this

, will consider the front end validation part.

Ewan Gilby

Kristen
Test

22859 Posts

Posted - 2010-11-01 : 10:02:18

"would like to know if this could turn out to be a disadvantage in any way"

Using


REPLACE(@p_request,' ','')

is slower than if you did not need to include the REPLACE function. If you don't need it very often, or for very many rows, its most probably not a problem. If your system gets busy, and you have to use REPLACE() in lots of queries, on lots of data, and the queries are used often, then it will slow your system.

You could use a trigger that removes any spaces from that column when the data is INSERTED or UPDATED, and then you won't have to worry about spaces in the ciolumn - because there won't be any

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-11-01 : 10:14:16

You should fix the front-end code to not allow invalid data. It's always better to fix data before it gets to the database.

Then update the table one-time to remove the spaces, and add a check constraint to the table to prevent it from happening again.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources