MS SQL Server MSEQ

Author

query

Tiffany

2006-10-28, 7:27 pm

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

Warren Brunk

2006-10-28, 7:27 pm

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@microso
ft.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
>

Tiffany

2006-10-28, 7:27 pm

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@microso
ft.com...
>
>
>

Hugo Kornelis

2006-10-28, 7:27 pm

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

Arnie Rowland

2006-10-28, 7:27 pm

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:fq52k2hgo2p6nfp
g4jq67bvkml3r3hnhhn@
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

Hugo Kornelis

2006-10-28, 7:27 pm

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

Arnie Rowland

2006-10-28, 7:27 pm

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:beb2k21s9ptltdq
sou2dojs4ejbmpdcbt4@
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

Michael Abair

2006-10-28, 7:27 pm

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%23G
HA.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:beb2k21s9ptltdq
sou2dojs4ejbmpdcbt4@
4ax.com...
>
>

Hugo Kornelis

2006-10-31, 12:25 am

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

Sponsored Links