query

hi,
Have a problem and hope someone can help.
I have a column which consists of numbers such as
123
235
456
888
etc
How can i write a simple query to change them to
00123
00235
00456
00888
etc
Kindly advise.
Thank you very much
you can concatenate the data using a + sign.
Select '00' + cast(ColumnNameHere as varchar(255)) from tablename
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
Tech Blog - www.technologyis.com
*/
"Tiffany" <Tiffany@.discussions.microsoft.com> wrote in message
news:47F589A4-FD90-42C0-8833-6D3D93E28C0A@.microsoft.com...
> hi,
> Have a problem and hope someone can help.
> I have a column which consists of numbers such as
> 123
> 235
> 456
> 888
> etc
> How can i write a simple query to change them to
> 00123
> 00235
> 00456
> 00888
> etc
> Kindly advise.
> Thank you very much
>
|||Thank you very much. What if in the column, there is also alphabets like
abc
rde
fgd
123
256
and I just want 00+ to apply to numbers only. What should I do? Kindly advise.
Thank you
"Warren Brunk" wrote:

> you can concatenate the data using a + sign.
> Select '00' + cast(ColumnNameHere as varchar(255)) from tablename
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> Tech Blog - www.technologyis.com
> */
>
> "Tiffany" <Tiffany@.discussions.microsoft.com> wrote in message
> news:47F589A4-FD90-42C0-8833-6D3D93E28C0A@.microsoft.com...
>
>
|||On Wed, 25 Oct 2006 22:30:01 -0700, Tiffany wrote:

>Thank you very much. What if in the column, there is also alphabets like
>abc
>rde
>fgd
>123
>256
>and I just want 00+ to apply to numbers only. What should I do? Kindly advise.
Hi Tiffany,
Use a CASE expression:
SELECT CASE
WHEN ColumnNameHere NOT LIKE '%[^0-9]%'
THEN '00' ELSE '' END + ColumnNameHere
FROM TableNameHere;
Hugo Kornelis, SQL Server MVP
|||Hugo,
Instead of combining NOT LIKE and [^0-9], wouldn't that be easier to understand as:
SELECT CASE
WHEN ColumnNameHere LIKE '%[0-9]%' THEN '00'
ELSE ''
END + ColumnNameHere
FROM TableNameHere;
(I've always hated double negatives...)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message news:fq52k2hgo2p6nfpg4jq67bvkml3r3hnhhn@.4ax.com...
> On Wed, 25 Oct 2006 22:30:01 -0700, Tiffany wrote:
>
> Hi Tiffany,
> Use a CASE expression:
> SELECT CASE
> WHEN ColumnNameHere NOT LIKE '%[^0-9]%'
> THEN '00' ELSE '' END + ColumnNameHere
> FROM TableNameHere;
> --
> Hugo Kornelis, SQL Server MVP
|||On Thu, 26 Oct 2006 13:41:15 -0700, Arnie Rowland wrote:

>Hugo,
>Instead of combining NOT LIKE and [^0-9], wouldn't that be easier to understand as:
>SELECT CASE
> WHEN ColumnNameHere LIKE '%[0-9]%' THEN '00'
> ELSE ''
> END + ColumnNameHere
>FROM TableNameHere;
>(I've always hated double negatives...)
Hi Arnie,
Easier to understand, but incorrect as well. Try it with '1s3' or 'af7'
as input data.
Double negatives are a bane for language, but a boon for SQL. Not only
for relational division, but also for constructing LIKE clauses. The
very limited regexp powers of LIKE don't allow for a test of "all
characters have to be numeric", but they do allow a test for "no
character may not be numeric".
Of course, if the length of the string is guaranteed to be always three
characters, you could also use LIKE '[0-9][0-9][0-9]'.
Hugo Kornelis, SQL Server MVP
|||I see your point Hugo.
Your illustration of using the double negative will cover all
possibilities -pure or mixed alphanumerics, whereas my method only covers
all alpha or all numeric -but not the mixed possibilities.
For the OP's situation as presented (all alpha or all numeric), either will
work, but your method is definitely more robust.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:beb2k21s9ptltdqsou2dojs4ejbmpdcbt4@.4ax.com...
> On Thu, 26 Oct 2006 13:41:15 -0700, Arnie Rowland wrote:
>
> Hi Arnie,
> Easier to understand, but incorrect as well. Try it with '1s3' or 'af7'
> as input data.
> Double negatives are a bane for language, but a boon for SQL. Not only
> for relational division, but also for constructing LIKE clauses. The
> very limited regexp powers of LIKE don't allow for a test of "all
> characters have to be numeric", but they do allow a test for "no
> character may not be numeric".
> Of course, if the length of the string is guaranteed to be always three
> characters, you could also use LIKE '[0-9][0-9][0-9]'.
> --
> Hugo Kornelis, SQL Server MVP
|||the isnumeric function is probably the most efficient way of accomplishing
this task
Michael Abair
Programmer / Analyst
Chicos FAS Inc.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23ZcV%23YZ%23GHA.4704@.TK2MSFTNGP04.phx.gbl.. .
>I see your point Hugo.
> Your illustration of using the double negative will cover all
> possibilities -pure or mixed alphanumerics, whereas my method only covers
> all alpha or all numeric -but not the mixed possibilities.
> For the OP's situation as presented (all alpha or all numeric), either
> will work, but your method is definitely more robust.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:beb2k21s9ptltdqsou2dojs4ejbmpdcbt4@.4ax.com...
>
|||On Fri, 27 Oct 2006 09:44:03 -0400, Michael Abair wrote:

>the isnumeric function is probably the most efficient way of accomplishing
>this task
Hi Michael,
Except that isnumeric returns 1 if the value passed to it can be
converted to at least one numeric datatype - not necessary int.
Try
SELECT ISNUMERIC('$'), ISNUMERIC('1E3'), ISNUMERIC('1.d2')
Hugo Kornelis, SQL Server MVP
sql