How To Sort Table In Sql2000 With Ipaddress(format X.x.x.x) As Column With Nvarchar Datatype In Ascending Order
How to sort table in sql2000 with ipaddress(format x.x.x.x) as column with nvarchar datatype in ascending order
without using stored procedure
Ex:
Table: netComputers(3 rows)
Column Name: ipAddress (string data type)
ipAddress
0.0.18.1
0.1.1.2
0.0.0.1
Sql query : if I use the query
Select ipAddress from netComputers order by cast( replace(ipaddress,'.','') as numeric(12)) asc
Gives result as :
ipAddress
0.0.0.1
0.1.1.2
0.0.18.1
Where as expected result should be:
ipAddress
0.0.0.1
0.0.18.1
0.1.1.2
View Complete Forum Thread with Replies
Related Forum Messages:
Column Sorting In Ascending Order
Hi I have Constructed a table Table Name : MasterEntry Column Name: MasterEntryNumber ServiceName ServiceDepartment EmployeeName MasterEntryNumber is GenerationNumber where ever any entry is happen in table if put a queries [select * From masterentry Order by MasterentryNumber] is give output in ascending order e.g MasterEntryNumber has 1 2 3 it give correct ordering but if i exceed more 10 if i put same queries Output of queries is 1 10 11 2 3 this is the out of the queries if it exceed more 10 rows i want queries should be look like this 1 2 3 ...... 10 did any have experience on this issue please let me know where i can fix kinds regards
View Replies !
SORT Order In SQL2000
How can I alter the default SORT order when SQL2000 originally installed ? --Is there a Store_Proc in SQL2000 to do this ? Thank you very much if you can help me !! Lewis
View Replies !
Dynamic Sort Column And Sort Order Not Working
I am trying to set sorting up on a DataGrid in ASP.NET 2.0. I have it working so that when you click on the column header, it sorts by that column, what I would like to do is set it up so that when you click the column header again it sorts on that field again, but in the opposite direction. I have it working using the following code in the stored procedure: CASE WHEN @SortColumn = 'Field1' AND @SortOrder = 'DESC' THEN Convert(sql_variant, FileName) end DESC, case when @SortColumn = 'Field1' AND @SortOrder = 'ASC' then Convert(sql_variant, FileName) end ASC, case WHEN @SortColumn = 'Field2' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, Convert(varchar(8000), FileDesc)) end DESC, case when @SortColumn = 'Field2' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), FileDesc)) end ASC, case when @SortColumn = 'VersionNotes' and @SortOrder = 'DESC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end DESC, case when @SortColumn = 'VersionNotes' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end ASC, case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, FileDataID) end DESC, case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'ASC' THEN CONVERT(sql_variant, FileDataID) end ASC And I gotta tell you, that is ugly code, in my opinion. What I am trying to do is something like this: case when @SortColumn = 'Field1' then FileName end, case when @SortColumn = 'FileDataID' then FileDataID end, case when @SortColumn = 'Field2' then FileDesc when @SortColumn = 'VersionNotes' then VersionNotes end case when @SortOrder = 'DESC' then DESC when @SortOrder = 'ASC' then ASC end and it's not working at all, i get an error saying: Incorrect syntax near the keyword 'case' when i put a comma after the end on line 5 i get: Incorrect syntax near the keyword 'DESC' What am I missing here? Thanks in advance for any help -Madrak
View Replies !
Default Sort Order When Order By Column Value Are All The Same
Hi, We got a problem. supposing we have a table like this: CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times) select top 5 * from a order by aName Result is: 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde select top 10 * from a order by aName Result is: 11Bank of abcde 10Bank of abcde 9Bank of abcde 8Bank of abcde 7Bank of abcde 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek: Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot. So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
View Replies !
Default Sort Order When The Order By Column Value Are All The Same
Hi, We got a problem. supposing we have a table like this: CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times) select top 5 * from a order by aName Result is: 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde select top 10 * from a order by aName Result is: 11 Bank of abcde 10 Bank of abcde 9 Bank of abcde 8 Bank of abcde 7 Bank of abcde 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot. So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
View Replies !
Sort Ascending Isn't Sort Ascending
Hi, have uncovered a curious problem with sort ascending in tables. In a table I'm grouping on CONTRACT_ID field. The grouping works fine but the sort acending on CONTRACT_ID doesn't. Here are the actual contract id's I have to sort: 0202018B 0202019C 0202020G 0202021H 0202022J 0202023K 0202800B The above list is how the Crystal Report I'm converting does this sort - i.e. the normal, common sense, intuitive answer. Here's how SSRS does the sort: 0202022J 0202021H 0202019C 0202800B 0202018B 0202023K 0202020G i.e. a completely non-intuitive, non common-sense answer! I can probably solve this by stripping the last character and converting to int via an expression, but why does one get a completely non-sensical answer with the standard values? What is the logic by which Microsoft have implemented sorting. It seems very odd to me. Can anyone shed any light on this? thanking you in advance, cheers, rob
View Replies !
Default Sort Order - Open Table - Select Without Order By
Hi! I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order. The table returns the data in the same order in SQL Manager "Open Table" So I started to wonder what deterimins the sort order when there is no order by clause ? I researched this for a bit but found no straight answers. My table has no PK, but an identiy column. Peace. /P
View Replies !
How To Sort Date Column In ASC Order?
hi all. i am getting screwy results when i try to order the "the_date" column of my RecentLogin table. The below select statement should order the "the_date" column in cronological ascending order (defaults to ASC when not specified) but it goes August, October, September. And, not August, September, October. Any ideas why this is happening? Thanks. PS - for what it is worth, this column is stored as a VARCHAR and not a DateTime Code: select * from RecentLogin order by the_date Quote: Aug 23 2006 3:00PM Oct 3 2006 9:45PM Oct 6 2006 2:24PM Oct 8 2006 6:57PM Oct 8 2006 7:27PM Oct 8 2006 9:42PM Oct 8 2006 11:15AM Oct 8 2006 11:35AM Oct 8 2006 11:45PM Oct 9 2006 1:13PM Oct 9 2006 10:47AM Oct 12 2006 7:30PM Oct 12 2006 10:01AM Oct 12 2006 12:28AM Oct 13 2006 4:34PM Oct 13 2006 9:32AM Oct 16 2006 8:01PM Oct 17 2006 9:22AM Sep 3 2006 11:10PM Sep 3 2006 11:12PM Sep 3 2006 11:12PM Sep 3 2006 11:15PM Sep 3 2006 11:15PM Sep 3 2006 11:16PM Sep 3 2006 11:16PM Sep 3 2006 11:17PM Sep 3 2006 11:17PM Sep 5 2006 1:29PM Sep 5 2006 1:30PM Sep 5 2006 2:32PM Sep 7 2006 8:49PM Sep 9 2006 10:48PM Sep 1 2006 3:00PM Sep 13 2006 3:14PM Sep 14 2006 5:07PM Sep 15 2006 8:46AM Sep 17 2006 9:40PM Sep 19 2006 2:39PM Sep 19 2006 2:44PM Sep 21 2006 9:31AM
View Replies !
Field Not Sorting In Ascending Order
Hi, I've created a website usiing asp.net and all the data are stored in sql front. All the item are sorted in ascending order except one record. The correct order should be MP61, MP100, MP200, but this record is retrieved as MP100, MP200, MP61. If the coding is wrong, all the order displayed is not in ascending order. We have hundreds of items, but why it happens to this particular record? Can anyone help? Thanks in advance
View Replies !
Sorting The Data In Ascending Order .
Hi , Hopw can i sort the data in the ascending order so that i can see the date of my log files imported at the top of my table . Right now my log files get imported and appended in the tables but they get appended in the descending order at the bottom of the last data . Is there any way to sort the data so that i can see it in the reverse way when i open my tables ? Many Thanks . Anita.
View Replies !
Default Sort Order Of The Data When Inserting From One Table To Another
I have a data load process that reads data from flat file into a Stage table in sql server. The order of the records in the stage table is exactly same as the order in the flat file. The identity column on the Stage table (which is also the clustered index) represents the exact line/row number of the data in the filat file. I perform some transformations on the data in the stage table and then insert it into a cumulative table which has a clustered index on an identity column again. When I do this, does the order of the data in the cumulative table be in the same order as the data in the stage table? Anyone, please let me know if I can rely on SQL server to maintain the same order or I will be forcing a sort order on the Identity column (clustered index) of the stage table when I insert the data into a cumulative table. Thanks in advance!!
View Replies !
Modify Nvarchar Datatype To Datatime Datatype
Hi, I imported a table from Accees to SQL 7 with data in it. I need to modify one of the datatype columns to "datetime" from nvarchar. I tried to convert it manually, in SQL Server Enterprise Manager tool, but it gave me an error. I also tried, creating another column "DATE2-datatype:datetime" and updating the column with the old one. UPDATE users SET DATE2 = DATE.. But it also faild,.. How can I modify the column? Thank you.
View Replies !
Force The &"ORDER BY&" To Be In Ascending Order??
I noticed the StockDate is not sorted in proper order, like ascending order... Code: select top 1000 CONVERT(char, StockDate, 101) AS StockDate, timestamp from tblpurchaseraw where accountid = '119' order by stockdate desc I noticed that StockDate is a datetime datatype so why does the month get ordered 1st, then day get ordered 2nd and year get ordered 3rd... The sample data is MM/DD/YYYY... So, how do I get it ordered propery by Year, Month then Day??
View Replies !
Text Datatype Vs Nvarchar Datatype
Hi guys.. i have so doubts in my mind and that i want to discuss with you guys... Can i use more then 5/6 fields in a table with datatype of Text as u know Text can store maximu data... ? acutally i am trying to store a very long strings values into the all fields. it's just popup into my mind that might be table structer would not able to store that my amount of data when u use more then 5/6 text datatypes... and another thing... is which one is better to use as data type "Text" or "varchar(max)"... ? if any article to read more about these thing,, can you refere to me... Thanks and looking forward.-MALIK
View Replies !
Sort COLUMN NAMES Of A Table
Hi all, Is there a way i can write a script that will sort the column names of my table? Example: Table 1 columns: First_Name Last_Name Address City Country I want to write a script that will sort them like this: Address City Country First_Name Last_Name
View Replies !
Inconsistent Sort Order Using ORDER BY Clause
I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name. Product: Microsoft SQL Server Express Edition Version: 9.00.1399.06 Server Collation: SQL_Latin1_General_CP1_CI_AS for example, create table test_sort ( description varchar(75) ); insert into test_sort values('Non-A'); insert into test_sort values('Non-O'); insert into test_sort values('Noni'); insert into test_sort values('Nons'); then execute the following selects: select * from test_sort order by cast( 1 as nvarchar(75)); select * from test_sort order by cast( description as nvarchar(75)); Resultset1 ---------- Non-A Non-O Noni Nons Resultset2 ---------- Non-A Noni Non-O Nons Any ideas?
View Replies !
Pass NVARCHAR (or Other String-type) Var As A Column Or Table Name In SQL
Hey folks, the question is fairly simple, unfortunately the answer has proven rather elusive. Is it possible to declare a variable which would then be used to identify either a column or table in an SQL statement? Here's a basic idea of what I'd like to do: DECLARE @myVar AS NVARCHAR(50) SELECT * FROM @myVar or DECLARE @myVar AS NVARCHAR(50) SELECT @myVar FROM MyTable I'm probably looking for some sort of built in function that will accept an argument here... like COLUMN(@myVar) or something of the like. I just don't know where to look...
View Replies !
What Is The ADO DataType For NVarchAR(max)
I can't seem to get nvarchar(max) to work with ADO 2.8 using sql native client. I am creating a stored procedure and every time i attempt to add a parameter to the command object of type nVarChar(max) I receive the error "Parameter object is improperly defined" here is the code to add the parameter cmd.Parameters.Append cmd.createparameter(@piComments,adLongVarWChar,adparaminput,,me.comments) adLongVarWChar is the ado data type i am using to map to the new nVarChar(max) but it does not appear to be working. Is this supported in ADO? I am using the sql native client connection to connect to the database as follows. pubStrConnectionString = "Provider=SQLNCLI;" _ & "Server=.sqlExpress;" _ & "Database=MyDBName;" _ & "Integrated Security=SSPI;" _ & "DataTypeCompatibility=80;" _ & "MARS Connection=True;" thanks
View Replies !
Alter Table - Change Column Datatype
Hi,I want to change the datatype of an existing column from char tovarbinary. When I run the "Alter Table" statement, I get thefollowing error message -Disallowed implicit conversion from data type char to data typevarbinary, table 'test.dbo.testalter', column 'col1'. Use the CONVERTfunction to run this query.Can the CONVERT function be used as part of an alter table/altercolumn? Is there another way besides renaming the table and creatinga new one?Thanks,Bruce
View Replies !
Error With Using Nvarchar Datatype
Hi, I have created a database using VWD to keep values of urls and have structured it as... Prefix (http://, network name), address(www.name.com), and name (name of address), the address field has been defined as a nvarchar(MAX). Most of the addresses updated into the address field work, except something like: www.java-scripts.net/javascripts/Image-Rollover-Script.phtml. I get this error: Cannot open user default database. Login failed.Login failed for user 'NETWORKNAMEASPNET'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.Login failed for user 'NETWORKNAMEASPNET'.Source Error: Line 1176: if (((this.Adapter.InsertCommand.Connection.State & System.Data.ConnectionState.Open) Line 1177: != System.Data.ConnectionState.Open)) { Line 1178: this.Adapter.InsertCommand.Connection.Open(); Line 1179: } Line 1180: try { I can insert something like www.google.com into the addresses field without any errors. Any ideas why?If it is a nvarchar type it should be able to except all sorts of characters??
View Replies !
Validate Nvarchar Datatype For A-Z And 0-9
Is there a function that can be used in sql 7.02000 that can validate data in a column of a table to verify that its characters are 'A-Z', 'a-z', or '0 - 9'. I am dynamically buildingaltering a table based on the information returned quering another table. But I would like to verify that the characters are valid types before using that 'name' for the new column in the table. Is there something similar to the IsNumeric function? Thanks.
View Replies !
Datatype Nvarchar(max) Not Accessible
Hi, trying to input create table T (c1 nvarchar(max)); in MS SQL Server Manangement Studio Express results in an error : Fehler beim Analysieren der Abfrage. [ Token line number = 1,Token line offset = 30,Token in error = max ] create table T (c1 nvarchar(4000)); is processed w/o errors. I´ve installed SQL Server 2005 Express Ed. SP2 Microsoft SQL Server Management Studio Express 9.00.3042.00 Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158) Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 Microsoft .NET Framework 2.0.50727.42 Betriebssystem 5.1.2600 Any ideas? Thanks in advance Werner
View Replies !
Converting From Nvarchar To Money Datatype
Hi there,I have a table named Action. This table has a column InPrice with datatypenvarchar(12). I want to change its datatype from nvarchar(12) to money. Ibrowsed through the values and removed any dots. Th column now has onlynumeric values (and commas for decimal values such as 105,8). When I try tochange the datatype from nvarchar to money, following mesage is displayed:ADO error: Cannot convert a char value to money. The char value hasincorrect syntax.How can I solve this problem? I cannot figure out which values are causingthis error.Thanks in advance,Burak
View Replies !
NVARCHAR Vs VARCHAR Datatype And Performance
We have few stored procedures that use nvarchar datatype, this was not issue on SQL server 7.0 but in 2000 becomes a big issue. For example query that runs for 3 minutes in SQL server 2000 by replacing NVARCHAR to VARCHAR the same query runs for 2 seconds. The biggest challenge that I have deals with tables and user-defined datatypes of NVARCHAR that has been bounded to the table. How can I alter those without data corruption?
View Replies !
Maximum Length For Nvarchar In SQL2000
Hi, I am using SQLSERVER2000. When storing data in to database ,it is taking/storing only 255 characters for all the datatypes like nvarchar,nchar,char,ntext,text, etc... I need to increase the maximum length of a field which takes 1000 characters & more. I already increased the field length to 2000, but it is taking/storing only 255 characters. Please help me in this................
View Replies !
Conversion Failed When Converting The Nvarchar Value To Datatype Int
Exception in one of the stored procs: Conversion failed when converting the nvarchar value to datatype int. After moving database from 2000 to 2005. Using backup and restore. Note I had the same issue after restoring the production database on my local server SQL2000->SQL2000. The problem was solved by restoring master and msdb from the production. But I can't do this on the SQL2005. This is not a collation issue as I have ensured collation is the same on all databases including the system ones. The database in question makes extensive use of user defined data types (Which I have recreated on the destination server). Can anyone please help? Raf
View Replies !
Implicit Conversion Of Datatype Text To Nvarchar Is Not Allowed.
I am facing a problem while using SQL Server with VB application.Implicit conversion from datatype text to nvarchar is not allowed.Use the convert function to run this query.When i see the trace file, i see one stored procedure called but nolines of code get executed, and immediately after that the ROLLBACKTRANSACTION occurs and the applications fails.But to my surprise i am able to do the same thing on a differentmachine using the same application and the same database on the sameserver with the same user id.Can anyone explain the reason of occurance of this problem.I require this very urgently, so i will be oblized if anyone can comeup with a quick response.Kind Regards,Amit Kumar
View Replies !
Problem With Varchar And Nvarchar Datatype In Linked Server
Hi, I am updating a remote table using linked server in sql server 2005. but in case of varchar and nvarchar i am getting an error : "OLE DB provider "SQLNCLI" for linked server "LinkedServer1" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Msg 16955, Level 16, State 2, Line 1 Could not create an acceptable cursor." thanks in advance. Thanks & Regards Pintu
View Replies !
Changing Column Order In A Table
This subject has been posted several times, but I haven't seen a goodanswer.Problem:I want to change the order of the columns in a table using T-SQL only.Explanation:After running your code, I want to see the following table...CREATE TABLE [dbo].[TableName] ([First_Column] [int] NULL ,[Second_Column] [varchar] (20) NULL) ON [PRIMARY]look like this...CREATE TABLE [dbo].[TableName] ([Second_Column] [varchar] (20) NULL ,[First_Column] [int] NULL) ON [PRIMARY]Limitations:Don't post if your post would fall in the following categories:1. If you don't think it can be done2. If you think Enterprise Manager is the only way to do this3. If you think I should just change the order of my Selectstatements4. If you want to state that order column doesn't matter in arelational database5. If you want to ask me why I want to do thisWish:Hopefully the answer WON'T involve creating a brand new table, movingthe data from old to new, dropping the old table, then renaming thenew table to the old name. Yes, I can do that. The table I'm workingwith is extremely huge -- I don't want to do the data juggling.Thanks in advance!
View Replies !
Alter Table And Column Order
Is it possible to add a column to a table using the "alter table"statement and specify where in the sequence of columns the new columnsits. If not is there any way to alter the order of columns using TSQLrather than Enterprise Manager / Design Table.TIALaurence Breeze
View Replies !
Order By Nvarchar
I have a scenario to sort on an nvarchar (50) field and I need to keep any changes to the sproc in the ORDER BY clause if possible. This field will contain strings such as... abc-217c, abc-15a, abc-9a, abc-7b, abc-17ar, etc... The issue I'm having is when the records are sorted, they are returned as... abc-15a, abc-17ar, abc-217c, abc-7b, abc-9a,etc...ordering numerically on the first numeric character in the string ie, 1,1,2,7,9) However, I need the numeric component to be treated as a whole number and order in this fashion... abc-7b, abc-9a, abc-15a, abc-17ar, abc-217c (7,9,15,17,217, etc) I feel pretty sure that this issue has come up before...can anybody provide a working example that would provide a simple(or not so simple) solution?
View Replies !
Does Column Order Matter When Creating A Table?
Does column order matter when creating a table? For example, Should NOT NULL columns always come before NULL columns? Should most frequently used columns always be near the top? What about text, ntext and image data types? Should they always appear near the end of the column order?
View Replies !
Order Of Nvarchar(50) Field
In my SQL 2005 database table Records, I have 3 fields, field1, field2, and field3 which are all nvarchar(50) fields. The value of field2 is something like this, MDB006-MD002-0004-3-2007. I would like to order this field but only use the 0004-3-2007 part of the field to order it. Is it possible to put the last 11 charachters (0004-3-2007) in another field and then order it using this new field?
View Replies !
Mass Table Structure Change For Column Order
Say you have an existing populated SQL 2005 database, with 700+ tables, and you want to just change the order of the columns inside every table. Short of manually building conversion scripts, anyone know an automated way to do this? I was thinking thru ways to do them all in one shot, and have tools like Erwin and DbGhost that could be used also. Basically moving some standard audit columns from the end of the tables to just after the PK columns. Thanks, Bruce
View Replies !
BCP And Sort Order
I need to copy the structure and data of an existing SQL 6.5 server to one with a different sort order. Normally, I would use the transfer tool to accomplish this, but the servers are on different networks. My question is, is BCP the answer? In other words, will the data copied via BCP from the sending server be able to be copied on the recieiving server. Also, is there a way to automatically generate the BCP statements for all tables? What I would really like is to be able to get at the scripts and data files created by the transfer tool.
View Replies !
SQL 6.5 Sort Order
We have about 90 servers we need to change the sort order on to become standard with our CIO group. Our current sort order is dictionary order - case insensitive. We need to change to dictionary order - case insensitive - accent insensitive. I know the standard procedure is to use transfer manager or bcp all data out out to files, then re-install SQL or rebuild master. Because of the size of this project, I would like to find an easier way to do this. If anyone has any ideas, please reply. Thanks, Chris
View Replies !
Sort Order ID's
I'm trying to setup a duplicate of an old SQL Server 4.2 server to put in place while we upgrade the server, but I can't get the sort-order right. I know the existing server uses sort order id 40, but I can't find which sort-order that corresponds to during the install process. If anyone can give me a system table that lists all the sort orders names and id's, or can tell me what the text name for sort order 40 is, I would be very grateful. Thanks, Rob.
View Replies !
|
TRACKER
