|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Left trimming little squares/spacesHi All
I have a table that is populated by an excel plugin. Some of the populated rows have spaces in front. In Enterprise Manager, they appear with little squares in front. I have been trying to LTRIM the whole column but there is no change. This is what i have been executing for a specifc row with these spaces: update Tbcnt set firstname = ltrim(firstname) where contactnumber = 100000 Is there any special way to trim these spaces. Thank you in advance. Can you find out what they are?
E.g. SELECT ASCII(SUBSTRING(column_name,1,1)) FROM Table Also, is the only symptom that you see the box in Enterprise Manager, but the data works fine in the applications that consume it? If so, just stop looking at the data in Enterprise Manager -- Query Analyzer is a better place anyway. And/or, have Excel trim the data BEFORE it sends it to the database. Show quote "MittyKom" <Mitty***@discussions.microsoft.com> wrote in message news:7B46E252-C632-45C3-9C51-D3EC402766BB@microsoft.com... > Hi All > > I have a table that is populated by an excel plugin. Some of the populated > rows have spaces in front. In Enterprise Manager, they appear with little > squares in front. I have been trying to LTRIM the whole column but there > is > no change. > > This is what i have been executing for a specifc row with these spaces: > update Tbcnt > set firstname = ltrim(firstname) > where contactnumber = 100000 > > Is there any special way to trim these spaces. Thank you in advance. > > > > > Hi Aaron
Thank you for the reply. I executed: SELECT ASCII(SUBSTRING(firstname,1,1)), CHAR(ASCII(SUBSTRING(firstname,1,1))) FROM contacthd where contactnumber = 100000 and the ASCII code came as 10. These spaces are causing problems when we mail merge this data and i dont know how easy it is to trim this data in excel. Right now i have to trim several tables and i will then work on triming this in excel. Your response is greatly appreciated Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > Can you find out what they are? > E.g. > > SELECT ASCII(SUBSTRING(column_name,1,1)) FROM Table > > Also, is the only symptom that you see the box in Enterprise Manager, but > the data works fine in the applications that consume it? If so, just stop > looking at the data in Enterprise Manager -- Query Analyzer is a better > place anyway. And/or, have Excel trim the data BEFORE it sends it to the > database. > > > > > > "MittyKom" <Mitty***@discussions.microsoft.com> wrote in message > news:7B46E252-C632-45C3-9C51-D3EC402766BB@microsoft.com... > > Hi All > > > > I have a table that is populated by an excel plugin. Some of the populated > > rows have spaces in front. In Enterprise Manager, they appear with little > > squares in front. I have been trying to LTRIM the whole column but there > > is > > no change. > > > > This is what i have been executing for a specifc row with these spaces: > > update Tbcnt > > set firstname = ltrim(firstname) > > where contactnumber = 100000 > > > > Is there any special way to trim these spaces. Thank you in advance. > > > > > > > > > > > > > UPDATE contacthd
SET firstname = SUBSTRING(firstname, 2, LEN(firstname)) WHERE ASCII(SUBSTRING(firstname,1,1)) = 10; Show quote "MittyKom" <Mitty***@discussions.microsoft.com> wrote in message news:BC258672-A921-4C3E-9BEC-C8EE9CC83E01@microsoft.com... > Hi Aaron > > Thank you for the reply. I executed: > SELECT ASCII(SUBSTRING(firstname,1,1)), > CHAR(ASCII(SUBSTRING(firstname,1,1))) FROM contacthd where contactnumber > = > 100000 > > > and the ASCII code came as 10. > > These spaces are causing problems when we mail merge this data and i dont > know how easy it is to trim this data in excel. Right now i have to trim > several tables and i will then work on triming this in excel. Your > response > is greatly appreciated > > > > "Aaron Bertrand [SQL Server MVP]" wrote: > >> Can you find out what they are? >> E.g. >> >> SELECT ASCII(SUBSTRING(column_name,1,1)) FROM Table >> >> Also, is the only symptom that you see the box in Enterprise Manager, but >> the data works fine in the applications that consume it? If so, just >> stop >> looking at the data in Enterprise Manager -- Query Analyzer is a better >> place anyway. And/or, have Excel trim the data BEFORE it sends it to the >> database. >> >> >> >> >> >> "MittyKom" <Mitty***@discussions.microsoft.com> wrote in message >> news:7B46E252-C632-45C3-9C51-D3EC402766BB@microsoft.com... >> > Hi All >> > >> > I have a table that is populated by an excel plugin. Some of the >> > populated >> > rows have spaces in front. In Enterprise Manager, they appear with >> > little >> > squares in front. I have been trying to LTRIM the whole column but >> > there >> > is >> > no change. >> > >> > This is what i have been executing for a specifc row with these spaces: >> > update Tbcnt >> > set firstname = ltrim(firstname) >> > where contactnumber = 100000 >> > >> > Is there any special way to trim these spaces. Thank you in advance. >> > >> > >> > >> > >> > >> >> >> Thank you so much Aaron. It worked.
Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > UPDATE contacthd > SET firstname = SUBSTRING(firstname, 2, LEN(firstname)) > WHERE ASCII(SUBSTRING(firstname,1,1)) = 10; > > > > "MittyKom" <Mitty***@discussions.microsoft.com> wrote in message > news:BC258672-A921-4C3E-9BEC-C8EE9CC83E01@microsoft.com... > > Hi Aaron > > > > Thank you for the reply. I executed: > > SELECT ASCII(SUBSTRING(firstname,1,1)), > > CHAR(ASCII(SUBSTRING(firstname,1,1))) FROM contacthd where contactnumber > > = > > 100000 > > > > > > and the ASCII code came as 10. > > > > These spaces are causing problems when we mail merge this data and i dont > > know how easy it is to trim this data in excel. Right now i have to trim > > several tables and i will then work on triming this in excel. Your > > response > > is greatly appreciated > > > > > > > > "Aaron Bertrand [SQL Server MVP]" wrote: > > > >> Can you find out what they are? > >> E.g. > >> > >> SELECT ASCII(SUBSTRING(column_name,1,1)) FROM Table > >> > >> Also, is the only symptom that you see the box in Enterprise Manager, but > >> the data works fine in the applications that consume it? If so, just > >> stop > >> looking at the data in Enterprise Manager -- Query Analyzer is a better > >> place anyway. And/or, have Excel trim the data BEFORE it sends it to the > >> database. > >> > >> > >> > >> > >> > >> "MittyKom" <Mitty***@discussions.microsoft.com> wrote in message > >> news:7B46E252-C632-45C3-9C51-D3EC402766BB@microsoft.com... > >> > Hi All > >> > > >> > I have a table that is populated by an excel plugin. Some of the > >> > populated > >> > rows have spaces in front. In Enterprise Manager, they appear with > >> > little > >> > squares in front. I have been trying to LTRIM the whole column but > >> > there > >> > is > >> > no change. > >> > > >> > This is what i have been executing for a specifc row with these spaces: > >> > update Tbcnt > >> > set firstname = ltrim(firstname) > >> > where contactnumber = 100000 > >> > > >> > Is there any special way to trim these spaces. Thank you in advance. > >> > > >> > > >> > > >> > > >> > > >> > >> > >> > > > Quick question. Why is it that the ordinary update stmt below did not work?
update Tbcnt set firstname = ltrim(firstname) where contactnumber = 100000 Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > UPDATE contacthd > SET firstname = SUBSTRING(firstname, 2, LEN(firstname)) > WHERE ASCII(SUBSTRING(firstname,1,1)) = 10; > > > > "MittyKom" <Mitty***@discussions.microsoft.com> wrote in message > news:BC258672-A921-4C3E-9BEC-C8EE9CC83E01@microsoft.com... > > Hi Aaron > > > > Thank you for the reply. I executed: > > SELECT ASCII(SUBSTRING(firstname,1,1)), > > CHAR(ASCII(SUBSTRING(firstname,1,1))) FROM contacthd where contactnumber > > = > > 100000 > > > > > > and the ASCII code came as 10. > > > > These spaces are causing problems when we mail merge this data and i dont > > know how easy it is to trim this data in excel. Right now i have to trim > > several tables and i will then work on triming this in excel. Your > > response > > is greatly appreciated > > > > > > > > "Aaron Bertrand [SQL Server MVP]" wrote: > > > >> Can you find out what they are? > >> E.g. > >> > >> SELECT ASCII(SUBSTRING(column_name,1,1)) FROM Table > >> > >> Also, is the only symptom that you see the box in Enterprise Manager, but > >> the data works fine in the applications that consume it? If so, just > >> stop > >> looking at the data in Enterprise Manager -- Query Analyzer is a better > >> place anyway. And/or, have Excel trim the data BEFORE it sends it to the > >> database. > >> > >> > >> > >> > >> > >> "MittyKom" <Mitty***@discussions.microsoft.com> wrote in message > >> news:7B46E252-C632-45C3-9C51-D3EC402766BB@microsoft.com... > >> > Hi All > >> > > >> > I have a table that is populated by an excel plugin. Some of the > >> > populated > >> > rows have spaces in front. In Enterprise Manager, they appear with > >> > little > >> > squares in front. I have been trying to LTRIM the whole column but > >> > there > >> > is > >> > no change. > >> > > >> > This is what i have been executing for a specifc row with these spaces: > >> > update Tbcnt > >> > set firstname = ltrim(firstname) > >> > where contactnumber = 100000 > >> > > >> > Is there any special way to trim these spaces. Thank you in advance. > >> > > >> > > >> > > >> > > >> > > >> > >> > >> > > > LTrim in SQL Server, at least, replaces white space. A line feed character,
again in SQL Server at least, is not white space. Show quote "MittyKom" <Mitty***@discussions.microsoft.com> wrote in message news:15F52079-B3FA-44EF-B2DD-771963FA8141@microsoft.com... > > Quick question. Why is it that the ordinary update stmt below did not > work? > > update Tbcnt > set firstname = ltrim(firstname) > where contactnumber = 100000 > > > "Aaron Bertrand [SQL Server MVP]" wrote: > >> UPDATE contacthd >> SET firstname = SUBSTRING(firstname, 2, LEN(firstname)) >> WHERE ASCII(SUBSTRING(firstname,1,1)) = 10; >> >> >> >> "MittyKom" <Mitty***@discussions.microsoft.com> wrote in message >> news:BC258672-A921-4C3E-9BEC-C8EE9CC83E01@microsoft.com... >> > Hi Aaron >> > >> > Thank you for the reply. I executed: >> > SELECT ASCII(SUBSTRING(firstname,1,1)), >> > CHAR(ASCII(SUBSTRING(firstname,1,1))) FROM contacthd where >> > contactnumber >> > = >> > 100000 >> > >> > >> > and the ASCII code came as 10. >> > >> > These spaces are causing problems when we mail merge this data and i >> > dont >> > know how easy it is to trim this data in excel. Right now i have to >> > trim >> > several tables and i will then work on triming this in excel. Your >> > response >> > is greatly appreciated >> > >> > >> > >> > "Aaron Bertrand [SQL Server MVP]" wrote: >> > >> >> Can you find out what they are? >> >> E.g. >> >> >> >> SELECT ASCII(SUBSTRING(column_name,1,1)) FROM Table >> >> >> >> Also, is the only symptom that you see the box in Enterprise Manager, >> >> but >> >> the data works fine in the applications that consume it? If so, just >> >> stop >> >> looking at the data in Enterprise Manager -- Query Analyzer is a >> >> better >> >> place anyway. And/or, have Excel trim the data BEFORE it sends it to >> >> the >> >> database. >> >> >> >> >> >> >> >> >> >> >> >> "MittyKom" <Mitty***@discussions.microsoft.com> wrote in message >> >> news:7B46E252-C632-45C3-9C51-D3EC402766BB@microsoft.com... >> >> > Hi All >> >> > >> >> > I have a table that is populated by an excel plugin. Some of the >> >> > populated >> >> > rows have spaces in front. In Enterprise Manager, they appear with >> >> > little >> >> > squares in front. I have been trying to LTRIM the whole column but >> >> > there >> >> > is >> >> > no change. >> >> > >> >> > This is what i have been executing for a specifc row with these >> >> > spaces: >> >> > update Tbcnt >> >> > set firstname = ltrim(firstname) >> >> > where contactnumber = 100000 >> >> > >> >> > Is there any special way to trim these spaces. Thank you in advance. >> >> > >> >> > >> >> > >> >> > >> >> > >> >> >> >> >> >> >> >> >> >Quick question. Why is it that the ordinary update stmt below did not work? LTRIM removes leading blanks. CHAR(10) is not a blank, it is a> >update Tbcnt >set firstname = ltrim(firstname) >where contactnumber = 100000 linefeed character. Roy Harvey Beacon Falls, CT Aaron and Roy, thank you so much.
Show quote "Roy Harvey" wrote: > >Quick question. Why is it that the ordinary update stmt below did not work? > > > >update Tbcnt > >set firstname = ltrim(firstname) > >where contactnumber = 100000 > > LTRIM removes leading blanks. CHAR(10) is not a blank, it is a > linefeed character. > > Roy Harvey > Beacon Falls, CT >
Show quote
"MittyKom" <Mitty***@discussions.microsoft.com> wrote in message Hi,news:7B46E252-C632-45C3-9C51-D3EC402766BB@microsoft.com... > Hi All > > I have a table that is populated by an excel plugin. Some of the populated > rows have spaces in front. In Enterprise Manager, they appear with little > squares in front. I have been trying to LTRIM the whole column but there > is > no change. > > This is what i have been executing for a specifc row with these spaces: > update Tbcnt > set firstname = ltrim(firstname) > where contactnumber = 100000 > > Is there any special way to trim these spaces. Thank you in advance. They probably are not spaces but some other character. Try: ASCII(firstname) to get the ASCII code of the character. then: replace(firstname, char(the value returned by ASCII(firstname)), '') You can replace the 2 operations with: replace(firstname, char(ASCII(firstname)), '') If you have more than 1 character to replace and they have different ASCII values, you can just run the Replace function again.
Show quote
"Raymond D'Anjou" <rdanjou@canatradeNOSPAM.com> wrote in message Forget the second solution I gave.news:ulKBF0sPGHA.1696@TK2MSFTNGP14.phx.gbl... > "MittyKom" <Mitty***@discussions.microsoft.com> wrote in message > news:7B46E252-C632-45C3-9C51-D3EC402766BB@microsoft.com... >> Hi All >> >> I have a table that is populated by an excel plugin. Some of the >> populated >> rows have spaces in front. In Enterprise Manager, they appear with little >> squares in front. I have been trying to LTRIM the whole column but >> there is >> no change. >> >> This is what i have been executing for a specifc row with these spaces: >> update Tbcnt >> set firstname = ltrim(firstname) >> where contactnumber = 100000 >> >> Is there any special way to trim these spaces. Thank you in advance. > > Hi, > They probably are not spaces but some other character. > Try: ASCII(firstname) > to get the ASCII code of the character. > then: > replace(firstname, char(the value returned by ASCII(firstname)), '') > > You can replace the 2 operations with: > replace(firstname, char(ASCII(firstname)), '') > > If you have more than 1 character to replace and they have different ASCII > values, you can just run the Replace function again. You really need to identify the ASCII code of the offending character(s). select ASCII(firstname) from Tbcnt where contactnumber = 100000 For example, lets say the character is a Tab (ASCII 9). Then do: update Tbcnt set firstname = replace(firstname, char(9), '') > replace(firstname, char(the value returned by ASCII(firstname)), '') Well, with firstname, this probably works. But for something like address, that might have valid cr/lf pairs embedded within, probably safer to just replace the first one... "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message Yeah I know. :-(news:u1Q6gDtPGHA.2052@TK2MSFTNGP10.phx.gbl... >> replace(firstname, char(the value returned by ASCII(firstname)), '') > > Well, with firstname, this probably works. But for something like > address, that might have valid cr/lf pairs embedded within, probably safer > to just replace the first one... I should have my first coffee before answering any posts. At least the poster has an idea now on how to identify the character(s).  |
|||||||||||||||||||||||
Other interesting topics