Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 13th, 2004, 06:26 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default Looks like a space but it isn't.

Hello all,

I just imported an Excel speadsheet into a SQL 2000 database. I'm trying to join that data with some other tables. I noticed that there is a space or what looks like a space after every entry in the column and wherever there is a space between words. I want to join to the data in but I cannot get rid of the space with the rtrim and ltrim functions. And replacing spaces with nothing wouldn't work, one because its not a space and two if it was a space and removed the spaces between words I wouldn't be able to join to the column.

I found a work-around by doing a 'left' on the length - 1 and ended up copying what looked like the space character into a 'replace' function and that worked.

Does anyone know what that character is? In the future I would simply like to replace that character with a space and then 'rtrim/ltrim' the data.

Thank you.

Cheers,
Richard

Reply With Quote
  #2 (permalink)  
Old December 14th, 2004, 01:28 AM
Friend of Wrox
Points: 2,473, Level: 20
Points: 2,473, Level: 20 Points: 2,473, Level: 20 Points: 2,473, Level: 20
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2004
Location: India
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
Default

I think it is not space but tab charecter. you can check for vbTab in replace function.

Om Prakash
Reply With Quote
  #3 (permalink)  
Old December 16th, 2004, 03:40 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Hi Om,

Thank you for your reply.

I tried replace with 'vbTab' and that doesn't remove the space either.

Any other suggestions?

What I was able to do though was to copy the space from the database and replace it with a "real" space from the 'space bar' on the keyboard for entries that have spaces between words. Never have I seen such a problem.

Thanks,
Richard

Reply With Quote
  #4 (permalink)  
Old September 7th, 2005, 07:17 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

There are a number of "extended asci" values that can appear as a blank. You need to find out what the value is then use the SQL replace function to get rid of it. Use the ascii() function to find out what it is first though. It would be something along these lines.

select char(255)
select ascii('x')

for the field and row where you get the blank/tab/???
select ascii(right(fieldx,1))

This should give you an asci value, whatever that value is put it in the statement below replacing the 255

REPLACE(fieldx,char(255),'')

This will replace the offending value with nothing, thus removing it.


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
space is not working.. gantait XSLT 5 February 26th, 2007 04:39 AM
space crmpicco Javascript How-To 1 February 7th, 2005 01:26 PM
Unwanted space... again Snib HTML Code Clinic 1 August 25th, 2004 04:45 PM
space hassles Adam H-W HTML Code Clinic 16 June 30th, 2004 04:09 PM
Space in URL lian_a Classic ASP Basics 5 June 23rd, 2004 11:29 PM



All times are GMT -4. The time now is 12:19 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.