| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2002-07-09 : 11:52:17
|
| I'm having a compare problem with usernames - I think I've tracked it down to name strings containing carriage returns.The queryselect '('+firstname+')'will return(name) for most namesand(name for othersI'm guessing the right parenthesis is missing due to a string termination like CR or LF. Any other possabilities?I've experience manupulating strings in other languages, but have no experience in SQL. Would someone point me to the right string functions that I could use to develop a query that would detect and correct these string anomolies?Sam |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-09 : 12:10:06
|
what is the datatype of firstname? if its a CHAR then you maybe seeing truncation.to flush out the cr/lf use the replace...select '(' + replace(replace(firstname,char(10),''),char(13),'') + ')' <O> |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-07-09 : 12:30:31
|
| Aged Yak Warrior, Thanks for the feedback. Seems that replace(firstname, char(10),'') does find the CR. Would you please suggest a query that can be constructed to find and correct these anomalies? Looks like this may be painful. The datatype is nvarchar (50) SamC |
|
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2002-07-09 : 12:32:46
|
| Are you sure your field contains only CR/LF? Is it possible that you might have other control characters in there? What about the ESC character (27) that can cause text malformation...Daniel Small MIAPwww.danielsmall.com IT Factoring |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-07-09 : 13:03:55
|
| I guess it's possible until proven otherwise. My experiments have been limited so far. Of course it's harder to expand the requirement to remove "ALL" non-printing characters from the string. SamC |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-07-09 : 16:18:51
|
| Not sure if the old Ltrim(rtrim(column)) will work. It is intended for spaces, but might work with CR/LF/ESC, etc...select '('+ltrim(rtrim(firstname))+')' If you try this, let me know the outcome.Jeremy |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-07-09 : 17:16:58
|
| When I experiment on live data, it would be great to have a command to copy the table to be experimented upon to a scratch table.I've been using import/export, but there's got to be an SQL command to create and copy table and contents to another tablename?SamC |
|
|
|
joshb
Yak Posting Veteran
52 Posts |
Posted - 2002-07-09 : 17:25:41
|
| Look up the SELECT INTO command in Books Online. |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-07-09 : 17:26:33
|
| Here's an example using tempdbuse tempdbgoselect * into tempCustomers from Northwind.dbo.Customersgoselect * from tempCustomersgoHTHJasper Smith |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-07-09 : 18:07:50
|
| If you are getting the UserName with a Windows API call, it may be NULL terminated.To kill the NULL, remove the last character in the string.DECLARE @username as VARCHAR(50)--I have "Z" as my null terminator that I want to remove--I don't know how to "type" a null :)SET @userName = 'MichaelPZ'SELECT SUBSTRING(@userName,1,Len(@userName)-1) as FixedUserNameMichael<Yoda>Use the Search page you must. Find the answer you will. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-07-09 : 18:30:08
|
| Thanks to everyone for their contributions to this topic today.For the record, here's how I corrected the data fields with CR...This fix assumes the CR / LF was at the end of the strings...UPDATE USERSSet Firstname = left(Firstname, Len(Firstname)-1)WHERE RIGHT(Firstname,1) = CHAR(10)The above query had to be run again using CHAR(13)And again for Lastname - It's done, the data's fixed.Thanks,SamC |
|
|
|
|
String problem in usernames?