| Author |
 Topic  |
|
|
Etymon
Advanced Member
United States
2385 Posts |
 Posted - 02 July 2009 : 06:30:32
|
I did a simple bulk insert, and it worked fine as far as landing the data in the columns correctly.
However, I have noticed that when I query a column of data for something specific, I have to use LIKE instead of = to find what I want. I looked closer at the import file, a .txt file, that I had previously exported from Excel, and I see that in many instances I have extra spaces before and/or after the data.
For instance, (I am using quotes to emphasize the problem) if I have the word "Etymon" that I am trying to find, my data may have been imported as " Etymon" or "Etymon " or " Etymon " or an exaggeration of any of those.
How might I do a trim or something to that effect as the data is going into the database while importing it? What is the correct terminology to search for in the MS SQL Books Online? I am using Microsoft SQL Server Management Studio Express. |
Edited by - Etymon on 02 July 2009 06:34:46 |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 02 July 2009 : 06:33:37
|
The trim() function in ASP will do exactly that; trim all leading and trailing spaces from a string:
"INSERT INTO TABLE (FIELD) VALUES ('"&trim(string)&"')"
<edit>There's a TRIM function in MySQL that may also be present in MSSQL that would save you having to run through inserting all the records again.</edit>
|
 Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
Edited by - Shaggy on 02 July 2009 06:34:48 |
 |
|
|
Etymon
Advanced Member
United States
2385 Posts |
Posted - 02 July 2009 : 06:37:50
|
Thanks Shaggy!
I am using Studio Express to do this:
BULK INSERT MyDatabase.MySchema.MyTable
FROM 'c:\MyFile.txt'
|
|
|
|
Etymon
Advanced Member
United States
2385 Posts |
Posted - 02 July 2009 : 06:40:20
|
Well, I see where TRIM is within the ODBC Reserved Keywords. The help section says this about ODBC Reserved Keywords:
The following words are reserved for use in ODBC function calls. These words do not constrain the minimum SQL grammar; however, to ensure compatibility with drivers that support the core SQL grammar, applications should avoid using these keywords.
Edit:
By the way, I am using Microsoft SQL Server 2005. |
Edited by - Etymon on 02 July 2009 06:42:11 |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 02 July 2009 : 06:52:38
|
| I don't think you can trim when doing a bulk insert, but what you could do is bulk insert into a temp table and then use an insert into query to import from the temp table into the final table and do the trim that way |
|
|
|
Etymon
Advanced Member
United States
2385 Posts |
Posted - 02 July 2009 : 06:58:24
|
| Ah, OK. Thanks Huw! I thought I was spinning my wheels there. Your thoughts give me something to munch on today. I will research it and see where I get with it. Thanks again to both of you guys! |
|
|
|
Etymon
Advanced Member
United States
2385 Posts |
Posted - 02 July 2009 : 07:16:21
|
I found the following regarding INSERT (Transact-SQL)
Rules for Inserting Rows
When you insert rows, the following rules apply:
If a value is being loaded into columns with a char, varchar, or varbinary data type, the padding or truncation of trailing blanks (spaces for char and varchar, zeros for varbinary) is determined by the SET ANSI_PADDING setting defined for the column when the table was created. For more information, see SET ANSI_PADDING (Transact-SQL).
The following table shows the default operation for SET ANSI_PADDING OFF.
Data type Default operation
char Pad value with spaces to the defined width of column.
varchar Remove trailing spaces to the last nonspace character
or to a single-space character for strings made up of
only spaces.
varbinary Remove trailing zeros.
|
Edited by - Etymon on 02 July 2009 07:17:24 |
|
|
|
Etymon
Advanced Member
United States
2385 Posts |
Posted - 02 July 2009 : 10:49:18
|
I took another look at how I was exporting my data from Excel and then cleaned up the trailing spaces there instead. Now my data in the SQL database is clean when I do the search I first mentioned earlier.
Thank again everyone! |
Edited by - Etymon on 02 July 2009 10:49:43 |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 02 July 2009 : 19:37:58
|
Doesn't sql server have a TRIM function built in that you could use with an UPDATE?
Anyway, glad you got everything solved.
|
======
Doug G
======
Computer history and help at www.dougscode.com |
|
|
| |
Topic |
|
Topic Locked
