Sql Server Alignment problem: project

Showing posts with label project. Show all posts

Monday, March 19, 2012

Alter Table - Add Colum - Ordinal Position?

I am using SQL Server 2000. As part of a new development project I find that
I frequently need to add new columns/fields to existing tables using SQL
Scripts in Query Analyzer. I would like to add the new column at a specific
Ordinal Position but when I use the standard Alter Table Add ... syntax for
adding a new column the column always gets added to the end of the table.
Accomplishing this task is relatively easy using Enterprise Manager, you
simply select the column where you want the new column to be placed and click
"Insert" and the new column gets inserted above the selected column. Is
there a way to add the column in a specific ordinal location from Query
Analyzer? If so, what is the syntax to make this happen. For example, I
want to add a new column ([columntwo] [int] NULL,) between [columnone] and
[columnthree] to the table layout below:
ColumnOne
ColumnThree
ColumnFour
Using standard "alter table' syntax [columntwo] get added to the bottom.
ColumnOne
ColumnThree
ColumnFour
ColumnTwo
I want it to look like this:
ColumnOne
ColumnTwo
ColumnThree
ColumnFour
I realize I can simply create a new table with the properly ordered fields,
copy the data over to the new table and then drop the existing table but this
is approach is not optimal.
--
So Much - Yet - So LittleTry running Profiler when you insert the column in Enterprise Manager. I
think you will find it does exactly what you said you don't want to do -
creates a new table, inserts the data from the old table, and then droops
the old table. The right thing to do here is to write your SQL statements
to not depend on a particular column order so that you can use Alter Table.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"SwingVoter" <swingvoter@.nospam.nospam> wrote in message
news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>I am using SQL Server 2000. As part of a new development project I find
>that
> I frequently need to add new columns/fields to existing tables using SQL
> Scripts in Query Analyzer. I would like to add the new column at a
> specific
> Ordinal Position but when I use the standard Alter Table Add ... syntax
> for
> adding a new column the column always gets added to the end of the table.
> Accomplishing this task is relatively easy using Enterprise Manager, you
> simply select the column where you want the new column to be placed and
> click
> "Insert" and the new column gets inserted above the selected column. Is
> there a way to add the column in a specific ordinal location from Query
> Analyzer? If so, what is the syntax to make this happen. For example, I
> want to add a new column ([columntwo] [int] NULL,) between [columnone] and
> [columnthree] to the table layout below:
> ColumnOne
> ColumnThree
> ColumnFour
> Using standard "alter table' syntax [columntwo] get added to the bottom.
> ColumnOne
> ColumnThree
> ColumnFour
> ColumnTwo
> I want it to look like this:
> ColumnOne
> ColumnTwo
> ColumnThree
> ColumnFour
> I realize I can simply create a new table with the properly ordered
> fields,
> copy the data over to the new table and then drop the existing table but
> this
> is approach is not optimal.
> --
> So Much - Yet - So Little|||Hi,
How many rows there in your table?
Creating a new table and dropping old one is only optimal option. This can
be achieved through Enterprose Manageralso , right click the table => choose
"Design Table" and drag and drop the new cloumn created by you from the end
position to desired Ordinal Position. But go for this only if the table rows
are only a few hundreds or else it will hang.
Thanks,
Sree
"Roger Wolter[MSFT]" wrote:
> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
> >I am using SQL Server 2000. As part of a new development project I find
> >that
> > I frequently need to add new columns/fields to existing tables using SQL
> > Scripts in Query Analyzer. I would like to add the new column at a
> > specific
> > Ordinal Position but when I use the standard Alter Table Add ... syntax
> > for
> > adding a new column the column always gets added to the end of the table.
> > Accomplishing this task is relatively easy using Enterprise Manager, you
> > simply select the column where you want the new column to be placed and
> > click
> > "Insert" and the new column gets inserted above the selected column. Is
> > there a way to add the column in a specific ordinal location from Query
> > Analyzer? If so, what is the syntax to make this happen. For example, I
> > want to add a new column ([columntwo] [int] NULL,) between [columnone] and
> > [columnthree] to the table layout below:
> > ColumnOne
> > ColumnThree
> > ColumnFour
> >
> > Using standard "alter table' syntax [columntwo] get added to the bottom.
> > ColumnOne
> > ColumnThree
> > ColumnFour
> > ColumnTwo
> >
> > I want it to look like this:
> > ColumnOne
> > ColumnTwo
> > ColumnThree
> > ColumnFour
> >
> > I realize I can simply create a new table with the properly ordered
> > fields,
> > copy the data over to the new table and then drop the existing table but
> > this
> > is approach is not optimal.
> > --
> > So Much - Yet - So Little
>
>|||Hi Roger,
ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
mysql does is there any option in Sql Server 2005.
Thanks,
Sree
"Roger Wolter[MSFT]" wrote:
> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
> >I am using SQL Server 2000. As part of a new development project I find
> >that
> > I frequently need to add new columns/fields to existing tables using SQL
> > Scripts in Query Analyzer. I would like to add the new column at a
> > specific
> > Ordinal Position but when I use the standard Alter Table Add ... syntax
> > for
> > adding a new column the column always gets added to the end of the table.
> > Accomplishing this task is relatively easy using Enterprise Manager, you
> > simply select the column where you want the new column to be placed and
> > click
> > "Insert" and the new column gets inserted above the selected column. Is
> > there a way to add the column in a specific ordinal location from Query
> > Analyzer? If so, what is the syntax to make this happen. For example, I
> > want to add a new column ([columntwo] [int] NULL,) between [columnone] and
> > [columnthree] to the table layout below:
> > ColumnOne
> > ColumnThree
> > ColumnFour
> >
> > Using standard "alter table' syntax [columntwo] get added to the bottom.
> > ColumnOne
> > ColumnThree
> > ColumnFour
> > ColumnTwo
> >
> > I want it to look like this:
> > ColumnOne
> > ColumnTwo
> > ColumnThree
> > ColumnFour
> >
> > I realize I can simply create a new table with the properly ordered
> > fields,
> > copy the data over to the new table and then drop the existing table but
> > this
> > is approach is not optimal.
> > --
> > So Much - Yet - So Little
>
>|||I don=B4t know why you need ordinal positions. Since youcan use the name
of a column picking the columns by the ordinal positions should not be
needed anymore, if you use a view for displaying the data or a
selection string,you can "reorder" the columns on your own, on the fly.
EM does this odd drop and create for alter, using QA does not, so if
there are many rows in your table use QA for this, rather than EM.
HTH, Jens Suessmeyer.|||Sreejith G wrote:
> Hi Roger,
> ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
> mysql does is there any option in Sql Server 2005.
> Thanks,
> Sree
>
No, there is no such option in SQL Server 2005.
As Roger explained, if you apply good practices like never using SELECT
* in your code then physical column order is relatively unimportant. As
far as end users are concerned, column order is determined by the
column list in a SELECT statement, not by the physical placement on
disc.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||:) you guys are right... there is no need of ordnial position. But just think
of a design with 1000+ tables. And having employees data in a table with
ordinal position,
1.Created Date
2.EmpFirstName
3.EmpLastName
4.UpdatedDate
5.DOB
6.Sex
7.EmpMiddleName
8.Location
And on the run you added few feilds and messed up all your tables like this.
In future a datawarehouse analyst might try to map OLTP feilds to OLAP design
to satisfy kep performance indicators and they might get blown away seeing
such positioning of table feilds.
Always below one make you feel good :)..
1.EmpFirstName
2.EmpMiddleName
3.EmpLastName
4.DOB
5.Sex
6.Location
7.Created Date
8.UpdatedDate
Thanks,
Sree
"Jens" wrote:
> I donÂ´t know why you need ordinal positions. Since youcan use the name
> of a column picking the columns by the ordinal positions should not be
> needed anymore, if you use a view for displaying the data or a
> selection string,you can "reorder" the columns on your own, on the fly.
> EM does this odd drop and create for alter, using QA does not, so if
> there are many rows in your table use QA for this, rather than EM.
> HTH, Jens Suessmeyer.
>|||:) you guys are right... there is no need of ordnial position. But just think
of a design with 1000+ tables. And having employees data in a table with
ordinal position,
1.Created Date
2.EmpFirstName
3.EmpLastName
4.UpdatedDate
5.DOB
6.Sex
7.EmpMiddleName
8.Location
And on the run you added few feilds and messed up all your tables like this.
In future a datawarehouse analyst might try to map OLTP feilds to OLAP design
to satisfy kep performance indicators and they might get blown away seeing
such positioning of table feilds.
Always below one make you feel good :)..
1.EmpFirstName
2.EmpMiddleName
3.EmpLastName
4.DOB
5.Sex
6.Location
7.Created Date
8.UpdatedDate
Thanks,
Sree
"Jens" wrote:
> I donÂ´t know why you need ordinal positions. Since youcan use the name
> of a column picking the columns by the ordinal positions should not be
> needed anymore, if you use a view for displaying the data or a
> selection string,you can "reorder" the columns on your own, on the fly.
> EM does this odd drop and create for alter, using QA does not, so if
> there are many rows in your table use QA for this, rather than EM.
> HTH, Jens Suessmeyer.
>|||> ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
> mysql does is there any option in Sql Server 2005.
You can submit this product enhancement request directly at
http://lab.msdn.microsoft.com/productfeedback/. Also specify the business
case for why this feature is important to you.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:1AD84F7E-9603-48DF-812E-D332EC40DC49@.microsoft.com...
> Hi Roger,
> ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
> mysql does is there any option in Sql Server 2005.
> Thanks,
> Sree
> "Roger Wolter[MSFT]" wrote:
>> Try running Profiler when you insert the column in Enterprise Manager. I
>> think you will find it does exactly what you said you don't want to do -
>> creates a new table, inserts the data from the old table, and then droops
>> the old table. The right thing to do here is to write your SQL
>> statements
>> to not depend on a particular column order so that you can use Alter
>> Table.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
>> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>> >I am using SQL Server 2000. As part of a new development project I find
>> >that
>> > I frequently need to add new columns/fields to existing tables using
>> > SQL
>> > Scripts in Query Analyzer. I would like to add the new column at a
>> > specific
>> > Ordinal Position but when I use the standard Alter Table Add ...
>> > syntax
>> > for
>> > adding a new column the column always gets added to the end of the
>> > table.
>> > Accomplishing this task is relatively easy using Enterprise Manager,
>> > you
>> > simply select the column where you want the new column to be placed and
>> > click
>> > "Insert" and the new column gets inserted above the selected column.
>> > Is
>> > there a way to add the column in a specific ordinal location from Query
>> > Analyzer? If so, what is the syntax to make this happen. For example,
>> > I
>> > want to add a new column ([columntwo] [int] NULL,) between [columnone]
>> > and
>> > [columnthree] to the table layout below:
>> > ColumnOne
>> > ColumnThree
>> > ColumnFour
>> >
>> > Using standard "alter table' syntax [columntwo] get added to the
>> > bottom.
>> > ColumnOne
>> > ColumnThree
>> > ColumnFour
>> > ColumnTwo
>> >
>> > I want it to look like this:
>> > ColumnOne
>> > ColumnTwo
>> > ColumnThree
>> > ColumnFour
>> >
>> > I realize I can simply create a new table with the properly ordered
>> > fields,
>> > copy the data over to the new table and then drop the existing table
>> > but
>> > this
>> > is approach is not optimal.
>> > --
>> > So Much - Yet - So Little
>>|||From a programming and data usability perspective you are correct. There is
no programming need for ordinal position however we have some tables with a
lot of fields and we want to keep "like" fields together for ease of mapping
and ease of visual recognition. It is much more difficult for a designer to
determine missing entities from the layout if the fields are scattered in an
non-logical order. As you can see from the list below in may cases simply
using a system table view and ordering by [name] is not helpful because the
similiar fields may not sort in that manner. It seems that there would be a
way to simply change the ordinal position in one of the master table as part
of the alter table script. Has anyone used this approach.
1.FirstName
2.MiddleName
.
7.City
8.State
Alter Table Add [LastName]
9.LastName
So Much - Yet - So Little
"Sreejith G" wrote:
> :) you guys are right... there is no need of ordnial position. But just think
> of a design with 1000+ tables. And having employees data in a table with
> ordinal position,
> 1.Created Date
> 2.EmpFirstName
> 3.EmpLastName
> 4.UpdatedDate
> 5.DOB
> 6.Sex
> 7.EmpMiddleName
> 8.Location
> And on the run you added few feilds and messed up all your tables like this.
> In future a datawarehouse analyst might try to map OLTP feilds to OLAP design
> to satisfy kep performance indicators and they might get blown away seeing
> such positioning of table feilds.
> Always below one make you feel good :)..
> 1.EmpFirstName
> 2.EmpMiddleName
> 3.EmpLastName
> 4.DOB
> 5.Sex
> 6.Location
> 7.Created Date
> 8.UpdatedDate
> Thanks,
> Sree
>
> "Jens" wrote:
> > I donÂ´t know why you need ordinal positions. Since youcan use the name
> > of a column picking the columns by the ordinal positions should not be
> > needed anymore, if you use a view for displaying the data or a
> > selection string,you can "reorder" the columns on your own, on the fly.
> > EM does this odd drop and create for alter, using QA does not, so if
> > there are many rows in your table use QA for this, rather than EM.
> >
> > HTH, Jens Suessmeyer.
> >
> >|||It seems that there would be a script that is available to simply change the
ordinal position of the field for that table in the master table as part of
the alter table script. I am not sure of the impacts of this method. Has
anyone used this approach.
--
So Much - Yet - So Little
"Roger Wolter[MSFT]" wrote:
> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
> >I am using SQL Server 2000. As part of a new development project I find
> >that
> > I frequently need to add new columns/fields to existing tables using SQL
> > Scripts in Query Analyzer. I would like to add the new column at a
> > specific
> > Ordinal Position but when I use the standard Alter Table Add ... syntax
> > for
> > adding a new column the column always gets added to the end of the table.
> > Accomplishing this task is relatively easy using Enterprise Manager, you
> > simply select the column where you want the new column to be placed and
> > click
> > "Insert" and the new column gets inserted above the selected column. Is
> > there a way to add the column in a specific ordinal location from Query
> > Analyzer? If so, what is the syntax to make this happen. For example, I
> > want to add a new column ([columntwo] [int] NULL,) between [columnone] and
> > [columnthree] to the table layout below:
> > ColumnOne
> > ColumnThree
> > ColumnFour
> >
> > Using standard "alter table' syntax [columntwo] get added to the bottom.
> > ColumnOne
> > ColumnThree
> > ColumnFour
> > ColumnTwo
> >
> > I want it to look like this:
> > ColumnOne
> > ColumnTwo
> > ColumnThree
> > ColumnFour
> >
> > I realize I can simply create a new table with the properly ordered
> > fields,
> > copy the data over to the new table and then drop the existing table but
> > this
> > is approach is not optimal.
> > --
> > So Much - Yet - So Little
>
>|||Hi,
This may be a silly question, what are the drawbacks to altering the
column order (colorder) through the syscolumns table?
- Ben|||Hi,
As Roger metioned, In SQL server, you need to create a new table, inserts
the data from the old table, and then drop the old table.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: Alter Table - Add Colum - Ordinal Position'
>thread-index: AcYtkhaya59bWEyOR5ylpD9wDQSW8A==>X-WBNR-Posting-Host: 207.59.213.130
>From: "=?Utf-8?B?U3dpbmdWb3Rlcg==?=" <swingvoter@.nospam.nospam>
>References: <91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com>
<#JBE6aTLGHA.3164@.TK2MSFTNGP11.phx.gbl>
>Subject: Re: Alter Table - Add Colum - Ordinal Position'
>Date: Thu, 9 Feb 2006 08:01:29 -0800
>Lines: 66
>Message-ID: <C2BE3CB0-839A-4CE9-9FDE-96BC31931745@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:420560
>X-Tomcat-NG: microsoft.public.sqlserver.server
> It seems that there would be a script that is available to simply change
the
>ordinal position of the field for that table in the master table as part
of
>the alter table script. I am not sure of the impacts of this method. Has
>anyone used this approach.
>--
>So Much - Yet - So Little
>
>"Roger Wolter[MSFT]" wrote:
>> Try running Profiler when you insert the column in Enterprise Manager.
I
>> think you will find it does exactly what you said you don't want to do -
>> creates a new table, inserts the data from the old table, and then
droops
>> the old table. The right thing to do here is to write your SQL
statements
>> to not depend on a particular column order so that you can use Alter
Table.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
>> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>> >I am using SQL Server 2000. As part of a new development project I
find
>> >that
>> > I frequently need to add new columns/fields to existing tables using
SQL
>> > Scripts in Query Analyzer. I would like to add the new column at a
>> > specific
>> > Ordinal Position but when I use the standard Alter Table Add ...
syntax
>> > for
>> > adding a new column the column always gets added to the end of the
table.
>> > Accomplishing this task is relatively easy using Enterprise Manager,
you
>> > simply select the column where you want the new column to be placed
and
>> > click
>> > "Insert" and the new column gets inserted above the selected column.
Is
>> > there a way to add the column in a specific ordinal location from Query
>> > Analyzer? If so, what is the syntax to make this happen. For
example, I
>> > want to add a new column ([columntwo] [int] NULL,) between [columnone]
and
>> > [columnthree] to the table layout below:
>> > ColumnOne
>> > ColumnThree
>> > ColumnFour
>> >
>> > Using standard "alter table' syntax [columntwo] get added to the
bottom.
>> > ColumnOne
>> > ColumnThree
>> > ColumnFour
>> > ColumnTwo
>> >
>> > I want it to look like this:
>> > ColumnOne
>> > ColumnTwo
>> > ColumnThree
>> > ColumnFour
>> >
>> > I realize I can simply create a new table with the properly ordered
>> > fields,
>> > copy the data over to the new table and then drop the existing table
but
>> > this
>> > is approach is not optimal.
>> > --
>> > So Much - Yet - So Little
>>
>|||> This may be a silly question, what are the drawbacks to altering the
> column order (colorder) through the syscolumns table?
Not a silly question at all, but a simple answer: Corrupt table.
Syscolumns isn't only for the engine in what order to retrieve the columns. But also it defines in
what order the columns are physically stored on disk (in pages, row structures).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <vanevery@.gmail.com> wrote in message
news:1139501596.454222.136580@.g47g2000cwa.googlegroups.com...
> Hi,
> This may be a silly question, what are the drawbacks to altering the
> column order (colorder) through the syscolumns table?
> - Ben
>|||A similar suggestion has already been submitted to the Product Feedback
Center. You can vote on this suggestion here:
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=ed13e43a-a55a-45ee-98fb-b56f330a2cfa
Razvan|||Tibor,
Thank you very much! This information makes the rest of the
information presented in this thread much more justifiable!|||On Thu, 9 Feb 2006 07:50:34 -0800, SwingVoter wrote:
>From a programming and data usability perspective you are correct.
(snip)
> It is much more difficult for a designer to
>determine missing entities from the layout if the fields are scattered in an
>non-logical order.
Hi SwingVoter,
The easiest way to solve this is to organize the columns in a logical
order in your documentation, but don't care about the order in the
database.
Designers should work from the logical model, not from the
implementation.
> It seems that there would be a
>way to simply change the ordinal position in one of the master table as part
>of the alter table script.
There isn't one. As Tibor pointed out - attempting to do this would
corrupt your database.
--
Hugo Kornelis, SQL Server MVP

Friday, February 24, 2012

Alphanumeric sorting

This is a problem for almost every project that I work on. I get a list of
location for water and/soil sampling which look like this
mw-1
mw-2
mw-3
mw-4
mw-10
mw-11
mw-20
mw-31
so I must use a character/text field. But when I sort this varchar filed, I
get
mw-1
mw-10
mw-11
mw-2
mw-20
mw-3
mw-31
mw-4
but I want a natural alphanumeric sorting order, hence
mw-1
mw-2
mw-3
mw-4
mw-10
mw-11
mw-20
mw-31
How can I acheive this in SQL?
Thanks.
Archerbagman3rd wrote:
...
> mw-1
> mw-2
> mw-3
> mw-4
> mw-10
> mw-11
> mw-20
> mw-31
> How can I acheive this in SQL?
How about ORDER BY CAST(SUBSTRING(ColName, 4, LEN(ColName)-3) AS INT) ?|||Assuming that "mw-" is always present...
Try this:
order by cast(replace([column_name], 'mw-', '') as int)
An even better solution would be to split this string into the two
individual pieces of information that it holds and store them in separate
columns.
This will definitely save you a lot of trouble in the future.
ML|||What do these codes represent? Assuming there is a predetermined set of
codes you can put them in their own table (I expect you would have a
table of them anyway) and add a "sequence" column to define the order.
If in fact they are not codes but numbers representing some numeric
value then you have a more fundamental design problem - that is, why
are you storing numeric values concatenated as strings in the first
place? If you can't fix the design you can in fact order on an
expression, like:
CAST(SUBSTRING(col,4,10) AS INT)
or
CAST(SUBSTRING(col,CHARINDEX('-',col)+1,10) AS INT)
David Portas
SQL Server MVP
--|||> What do these codes represent? Assuming there is a predetermined set of
> codes you can put them in their own table (I expect you would have a
> table of them anyway) and add a "sequence" column to define the order.
Adding a "sequence" column to define the order has been my workaround for
now, but it is long and tedious, especially when I have hundreds of wells
They are monitoring wells, and I have no control over the naming
conventions. Sometimes they use dashes, sometimes they dont. Sometimes the
y
call them emw(extraction monitoring well), and I also have soil borings too.
sb-1
sb-2
sometimes they use
esb-1
esb-2
So, essentially, I cannot rely on a certain number of characters being text,
then numbers, etc.
I have any idea of how to do this, but it seems awkward and kludgian.
Essentially, I would set up a cursor to loop through every character of ever
y
row, and break up the rows into sections every time it changed from text to
numeric and vice versa, then order by the sections that I created.
Has anyone done this before and/or is there a better way?
Thanks.
Archer
"David Portas" wrote:

> What do these codes represent? Assuming there is a predetermined set of
> codes you can put them in their own table (I expect you would have a
> table of them anyway) and add a "sequence" column to define the order.
> If in fact they are not codes but numbers representing some numeric
> value then you have a more fundamental design problem - that is, why
> are you storing numeric values concatenated as strings in the first
> place? If you can't fix the design you can in fact order on an
> expression, like:
> CAST(SUBSTRING(col,4,10) AS INT)
> or
> CAST(SUBSTRING(col,CHARINDEX('-',col)+1,10) AS INT)
> --
> David Portas
> SQL Server MVP
> --
>|||How about :
ORDER BY
LEFT(col, PATHINDEX('%[0-9]%', col) - 1),
CAST(SUBSTRING(col, PATHINDEX('%[0-9]%', col), 8000) AS INT)
BG, SQL Server MVP
www.SolidQualityLearning.com
"bagman3rd" wrote:
> Adding a "sequence" column to define the order has been my workaround for
> now, but it is long and tedious, especially when I have hundreds of wells
> They are monitoring wells, and I have no control over the naming
> conventions. Sometimes they use dashes, sometimes they dont. Sometimes t
hey
> call them emw(extraction monitoring well), and I also have soil borings to
o.
> sb-1
> sb-2
> sometimes they use
> esb-1
> esb-2
> So, essentially, I cannot rely on a certain number of characters being tex
t,
> then numbers, etc.
> I have any idea of how to do this, but it seems awkward and kludgian.
> Essentially, I would set up a cursor to loop through every character of ev
ery
> row, and break up the rows into sections every time it changed from text t
o
> numeric and vice versa, then order by the sections that I created.
> Has anyone done this before and/or is there a better way?
> Thanks.
> Archer
> "David Portas" wrote:
>|||That is a step in the right direction, but as I said before, I have no
control over the naming of these wells. So, they sometimes switch from text
to numeric to numeric to text, etc. i.e.
mw-1-d-050505
The d is for duplicate and 050505 is a date. So,
ORDER BY
LEFT(col, PATHINDEX('%[0-9]%', col) - 1),
CAST(SUBSTRING(col, PATHINDEX('%[0-9]%', col), 8000) AS INT)
works, but not if I have text following my number. What would I do to take
this to the next level of being able to handle strings with multiple
text-num-text-num formats?
Thanks.
Archer
"Itzik Ben-Gan" wrote:
> How about :
> ORDER BY
> LEFT(col, PATHINDEX('%[0-9]%', col) - 1),
> CAST(SUBSTRING(col, PATHINDEX('%[0-9]%', col), 8000) AS INT)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "bagman3rd" wrote:
>|||So now you need alpha, numeric and date sorting from your code!
Obviously the encoding is badly designed. Unless you can tell us the
logic by which you would sort it I don't see how you can expect to code
that logic. I.e. which characters count as delimiters? which way do
dates sort? how do you distinguish a date from a number? More
importantly, since a human being would probably have great difficulty
doing the same thing your sorting is probably going to be very
difficult to read even if you can achieve it - thus defeating the
purpose of the sort in the first place.
Itzik's example should give you a start. You can extend that kind of
logic to as many parts and sub-parts as you like - assuming there is
some logic to be found in the code. I would use something like that to
populate your sequence column in a table each time you find a new code.
That way, if the sort sequence doesn't suit you can easily tweak it to
make the order more user-friendly.
David Portas
SQL Server MVP
--|||You can treat the date as a number. As I stated before, I have NO control
over how the well/soil boring sampling locations are named. I just want the
m
sorted in natural sorting order. I can find functions in scripting language
s
to do this, such as
http://maconlinux.net/php-online-ma...on.natsort.html
natsort
(PHP 4 , PHP 5)
natsort -- Sort an array using a "natural order" algorithm
Description
bool natsort ( array &array )
This function implements a sort algorithm that orders alphanumeric strings
in the way a human being would while maintaining key/value associations. Thi
s
is described as a "natural ordering". An example of the difference between
this algorithm and the regular computer string sorting algorithms (used in
sort()) can be seen below:
I cant believe that no one has run into this problem before, and someone
does not already have this figured out.
As stated, I would like to order alphanumeric strings in the way a human
being would.
I will try to extend the Itzik's example.
Thanks.
Archer
"David Portas" wrote:

> So now you need alpha, numeric and date sorting from your code!
> Obviously the encoding is badly designed. Unless you can tell us the
> logic by which you would sort it I don't see how you can expect to code
> that logic. I.e. which characters count as delimiters? which way do
> dates sort? how do you distinguish a date from a number? More
> importantly, since a human being would probably have great difficulty
> doing the same thing your sorting is probably going to be very
> difficult to read even if you can achieve it - thus defeating the
> purpose of the sort in the first place.
> Itzik's example should give you a start. You can extend that kind of
> logic to as many parts and sub-parts as you like - assuming there is
> some logic to be found in the code. I would use something like that to
> populate your sequence column in a table each time you find a new code.
> That way, if the sort sequence doesn't suit you can easily tweak it to
> make the order more user-friendly.
> --
> David Portas
> SQL Server MVP
> --
>|||As people have shown you, you can pull out the substring or set up a
look-up table for the sorting order. However, I would suggest
designing the codes to have leading zeroes in the right places. People
forget it takes time to design codes and often just whip up a
sequential list or a varyng lengh, mixed alphanumeric string.

allows blank value for integer Parameter

Hi have a problem to solve and I hope that this is not a SSRS Bug.

I created a Reports(using SQL Server Project) which has several parameters which values are passed to a SP.

One of these parameter is an Integer and it is an optional value, so if the user fill it is used by the SP, otherwise the SP uses NULL and run anyway.

I starts to define tha parameter:

Datatype = integer

Allow blank value

Available: Non queried

Default: Null

if I want to Preview the report I have to provide an integer to the parameter's field ...

If for instance I set:

Default: Not queried = 0

In the moment I deploy and I use the ReportViewer in my window application the parameter's field is unabled!!

So I tried this solution:

Datatype = integer

Allow blank value

Allow null value

Available: Non queried

Default: Null

In the preview the checkbox: NULL is checked and I click on the View Report.

But when I deploy it,in the ReportViewer in my window application the parameter's field this checkbox is unchecked.

Do I forget something during my setting?I have to control it programmatically?

N.B. By default the user will not user this parameter so the best is that he can click directly on "View Report" without any additional "work" on the parameter!!

Thank you for any help!

hi,

On the report parameters form do this please:

Add a parameter, name it to something,

choose data type integer,

mask as checked the "Allow null value",

select null as default value.

It was working on that way on my box as you wished. I'm using SSRS 2005 wih SP2

Regards,

Janos

Sunday, February 19, 2012

Allowing a conenction to a SQL Server 2005 database from another computer on a LAN

I am working with one other person on a VS 2005 vb.net web project that accesses SQL Server 2005. Both the computers are connected and my partner can run the application on his computer from his VS 2005 but we are getting an error on our first databind to a gridview on the page we are trying to run the error is below

A connection was successfully established with the
server, but then an error occurred during the
pre-login handshake. When connecting to SQL Server
2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow
remote connections. (provider: Named Pipes Provider,
error: 0 - No process is on the other end of the pipe.)

I check the properties of the SQL Server and the check box is checked that says allow remote access. I am not sure what to do.

Hi,

First, please try to allow remote connections for TCP/IP and Named Pipe according to the following KB article.

http://support.microsoft.com/kb/914277/en-us

If that still doesn't work, you can check the following link for troubleshooting.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=275050&SiteID=1

Monday, March 19, 2012

Alter Table - Add Colum - Ordinal Position?

Alter Table - Add Colum - Ordinal Position?

Alter Table - Add Colum - Ordinal Position?

Friday, February 24, 2012

Alphanumeric sorting

allows blank value for integer Parameter

Sunday, February 19, 2012

Allowing a conenction to a SQL Server 2005 database from another computer on a LAN

Sql Server Alignment problem

Blog Archive

About Me