|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Trim leading 000 from varchar fieldI have a varchar field with leading 0's as in the example below
00389948 00009348 09984903 How can I strip the leading 0's and still maintain the varchar field? You want to update the table?
UPDATE table SET col = CONVERT(VARCHAR(12), CONVERT(INT, col)) You just want to select that way? SELECT CONVERT(VARCHAR(12), CONVERT(INT, col)) FROM table However, this begs the questions, (a) how often are you going to run this UPDATE statement, or how many places will you have to change existing SELECT code? and, (b) if this is storing integers, why on earth would you need to maintain it as a varchar column? Why was it varchar in the first place? Show quote "Unix_to_Windows" <Unix_to_Wind***@discussions.microsoft.com> wrote in message news:0FF4543E-345A-4E83-93A9-AB07D35DB24B@microsoft.com... >I have a varchar field with leading 0's as in the example below > > 00389948 > 00009348 > 09984903 > > How can I strip the leading 0's and still maintain the varchar field? Thanks Aaron for your response and to answer your questions, the update needs
to run once and b.) I have no idea why the application needs a varchar column, I don't think the author of the application knows why either. Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > You want to update the table? > > UPDATE table SET col = CONVERT(VARCHAR(12), CONVERT(INT, col)) > > You just want to select that way? > > SELECT CONVERT(VARCHAR(12), CONVERT(INT, col)) FROM table > > However, this begs the questions, (a) how often are you going to run this > UPDATE statement, or how many places will you have to change existing SELECT > code? and, (b) if this is storing integers, why on earth would you need to > maintain it as a varchar column? Why was it varchar in the first place? > > > > > "Unix_to_Windows" <Unix_to_Wind***@discussions.microsoft.com> wrote in > message news:0FF4543E-345A-4E83-93A9-AB07D35DB24B@microsoft.com... > >I have a varchar field with leading 0's as in the example below > > > > 00389948 > > 00009348 > > 09984903 > > > > How can I strip the leading 0's and still maintain the varchar field? > > > >> How can I strip the leading 0's and still maintain the varchar field? See if this expression helps:RIGHT( col, LEN( col ) - PATINDEX( '%[^0]%', col ) + 1 ) -- Anith After further review of the 7 million rows I have discovered that about 80 of
the rows contain alpha numeric fields which cannot be updated. Show quote "Unix_to_Windows" wrote: > I have a varchar field with leading 0's as in the example below > > 00389948 > 00009348 > 09984903 > > How can I strip the leading 0's and still maintain the varchar field? So use WHERE ISNUMERIC(col)=1
(See http://www.aspfaq.com/2390 for limitations) And/or change the values that violate so that they no longer violate, and add a constraint so that somone can't enter an alpha tomorrow or next week... Show quote "Unix_to_Windows" <UnixtoWind***@discussions.microsoft.com> wrote in message news:EBCDF848-2A86-4E24-ACBB-F9C711E9194D@microsoft.com... > After further review of the 7 million rows I have discovered that about 80 > of > the rows contain alpha numeric fields which cannot be updated. > > "Unix_to_Windows" wrote: > >> I have a varchar field with leading 0's as in the example below >> >> 00389948 >> 00009348 >> 09984903 >> >> How can I strip the leading 0's and still maintain the varchar field? Perfect thanks
Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > So use WHERE ISNUMERIC(col)=1 > (See http://www.aspfaq.com/2390 for limitations) > > And/or change the values that violate so that they no longer violate, and > add a constraint so that somone can't enter an alpha tomorrow or next > week... > > > "Unix_to_Windows" <UnixtoWind***@discussions.microsoft.com> wrote in message > news:EBCDF848-2A86-4E24-ACBB-F9C711E9194D@microsoft.com... > > After further review of the 7 million rows I have discovered that about 80 > > of > > the rows contain alpha numeric fields which cannot be updated. > > > > "Unix_to_Windows" wrote: > > > >> I have a varchar field with leading 0's as in the example below > >> > >> 00389948 > >> 00009348 > >> 09984903 > >> > >> How can I strip the leading 0's and still maintain the varchar field? > > > Yet another solution below. Works properly as long as the value doesn't
contain spaces. UPDATE table SET col = Replace(LTrim(Replace(Col,'0',' ')),' ','0') Gert-Jan Unix_to_Windows wrote: Show quote > > I have a varchar field with leading 0's as in the example below > > 00389948 > 00009348 > 09984903 > > How can I strip the leading 0's and still maintain the varchar field?  |
|||||||||||||||||||||||
Other interesting topics