SQL Server 2012 :: Sorting By Alphanumeric Table Column Of NVARCHAR Datatype
Sep 24, 2015
I am trying to sort my sql resultset by an alphanumeric column of a table which is of NVARCHAR datatype. The sample data is given below:
CREATE TABLE #Activities(activityName NVARCHAR(100))
INSERT INTO #Activities VALUES('Field phase S14-04932-01')
INSERT INTO #Activities VALUES('Phase reporting')
INSERT INTO #Activities VALUES('Phase running')
INSERT INTO #Activities VALUES('RD1')
[Code] ....
The output of the query is like this:
A1
A2
A3
A4
E1 0DAA1
E10
E2 0DAA2
[Code] .....
The output what I require is this:
A1
A2
A3
A4
E1 0DAA1
E2 0DAA2
[Code] ....
View 9 Replies
ADVERTISEMENT
Apr 19, 2006
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 18 Replies
View Related
Jun 5, 2014
In Oracle when i create any procedure i define parameter datatype linked to under lying table.
For ex
create procedure testprocedure
(param1 customer.name%type,
param2 customer.salary%type )
Here i have defined param1 and param2 with datatype of name and salary of customer table respectively.
This way i do not need to worry about modifying param1 and param2 datatypes when datatypes of name & salary of customer table changes in future.
How can i accomplish this in SQL server.
View 2 Replies
View Related
Oct 1, 2014
I need to auto increment an alphanumeric field in a table.
The process is as follows:
1. First position is static letter C for contact or static letter A for account - field contact_id
2. The following 6 positions are numeric - example of the string would be C004658
3. When new contact is entered, it should look up the highest numeric value (in this case 004658) and increment it by one - resulting in C004659
View 9 Replies
View Related
May 1, 2015
I have a column A as 'text' datatype. This has CR-LF in between data.How to remove it.
I am trying to copy result from SQL to Excel
Eg:
A CR LF
ABC CR LF
DVB CR LF
The output should be:
A
ABC DVB
View 2 Replies
View Related
Mar 12, 2014
I have an employee table
Surname GivenName
ABC x.yz
A.BC X.*YZ
A*.BC xyz
The query needs to get me the surname and givenname which have more than 1 non alphanumeric characters. In this case the output will be
a.bc x.*yz
a*.BC xyz
I know how to find non alphanumeric chars in a string but not sure if I can how to find the strings with minimum 2 non alphanumeric chars?
View 9 Replies
View Related
Feb 25, 2015
I am having some difficulty getting a query to output an alpha numeric range grouping.
I have this data set:
Despatch_id Sample_ID
MIR00831 MCR0005752
MIR00831 MCR0005753
MIR00831 MCR0005754
MIR00831 MCR0005755
MIR00831 MCR0005756
MIR00831 MCR0005757
[code]....
Output:
DESPATCH_ID SAMPLE_ID_FROM SAMPLE_ID_TO
MIR00831 MCR0005752 MCR0005762
MIR00831 MCR0005803 MCR0005806
MIR00831 MCR0005808 MCR0005813
They need to be grouped by range specific to the alpha numeric part, which can vary within the same despatch. I was thinking of using a row over partition after splitting the numeric and alpha part and to check if they are consecutive and build the range. But I am thinking that this approach is an overkill and there may be a better way to achieve this in SQL 2012.
I have included the create table scripts and example data below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SAMPLE_TABLE](
[DESPATCH_ID] [nvarchar](30) NOT NULL,
[SAMPLE_ID] [nvarchar](30) NOT NULL
[code]....
View 7 Replies
View Related
Mar 14, 2008
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 10 Replies
View Related
Mar 14, 2006
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 2 Replies
View Related
Feb 25, 2008
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 5 Replies
View Related
Jun 6, 2007
Hi all,
I am having an issue which I'm guessing everyone has at one point. I just can't seem to find the answer. I might be misunderstanding something as I'm relatively new to SQL and data types etc.
Here is my issue.
I have a table with a column that has a nvarchar data type. The following is an example of field values in that column.
asystem1
zsystem19
zsystem9
bsystem23
bsystem3
I would like to sort that data alphabetically and then by number like so.
asystem1
bsystem3
bsystem23
zsystem9
zsystem19
A basic ORDER BY obviously will sort it alphabetically but will put 23 before 3. These system names are already named and can't be changed otherwise I would use "0" in front of single digit numbers.
I just can't figure out how to accomplish this. Is there a convert or cast (unfamiliar with those functions) that I can do or does it involve something more sophisticated?
I appreciate any help in advance.
Israel
View 6 Replies
View Related
Nov 26, 2007
Hi, I have a table whose Identifying column is not an integer but rather a manually entered id. (i.e. 106F, 106-09, G11 etc.) When sorted ascending, a G11 will come before a G2 in the list, 106-11 before 106-2, etc.I would like to insert a new column in the database or use some kind of function when sorting the database to ensure that the list in returned in the proper order. Any ideas?Much appreciated...
View 16 Replies
View Related
May 26, 2014
I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
View 1 Replies
View Related
Sep 18, 2014
A column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy.
I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.
The text before ;# (35106) should be inserted in to another column of the same table.
Below is the table structure:
create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
(
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int
)
View 2 Replies
View Related
Apr 9, 2008
Hi All,
I want to sort the column data which is of positive and negative number like the following.
-5823
-1
200
100
i want to sort like
-1
100
200
-5823
How can i do that can any one help me..
Thanks in advance.
-john
View 10 Replies
View Related
Mar 21, 2006
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 9 Replies
View Related
Jun 21, 2004
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 1 Replies
View Related
Jul 3, 2004
I want get DataType of Column in Table
ex: nvarchar, varchar, etc...
Help me !!
View 2 Replies
View Related
Sep 10, 2007
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 11 Replies
View Related
Mar 20, 2007
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 2 Replies
View Related
Oct 6, 2014
I am trying to use a stored procedure to update a column in a sql table using the value from a variable table I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.
This is my statement:
UPDATE [dbo].[sessions_teams] stc
SET stc.[Talks] = fmt.found_talks_type
FROM @Find_Missing_Talks fmt
WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks)
AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)
View 2 Replies
View Related
Jul 22, 2006
Hi,
How do we find the "column name" and "data type" of all the columns in a table. Assuming that I know the Table name or Table Object ID. I am using Microsoft SQL Server 2000.
Thanks
-Sudhakar
View 3 Replies
View Related
May 15, 2005
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 4 Replies
View Related
Aug 20, 2001
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 2 Replies
View Related
May 16, 2008
Hi,
how to convert nvarchar datatype to float?
Regards
Prashant
View 10 Replies
View Related
Apr 9, 2008
I have set a column called "Anumber", I am
using a computed value to get the PK ID # and
a the letter "A" to it.
('A'+CONVERT([varchar](10),[requestid],(0)))
The above works fine unless I copy a record,
The copied record never displays the correct
value like "A55" it keeps "NULL" as it value.
Is their a better way to achieve a result as
described above ?
I am coming from Foxpro to SQL, Any advice would
be great.
Thanks
View 6 Replies
View Related
Jan 11, 1999
Can anyone tell me how to change the datatype of a column.
Without dropping and recreating the table...
The table is empty...
THNK YOU VERY MUCH...
View 1 Replies
View Related
Apr 1, 2008
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 9 Replies
View Related
Jun 21, 2014
my table payment_details structure is
payment_id payment_code
1 null
2 null
3 null
4 null
here payment_id is a primary key and i need to update the whole payment_id column to payment_code column.so i just tried the below query
update payment_details
set payment_code = payment_no
where payment_code is null
but it shows subquery error?
View 3 Replies
View Related
Jul 24, 2015
I have a column name DateofRecord and it is nvarchar type..all the values in this column are like this
"04/24/2013'
"05/01/2014"...etc...
My requirement is to convert this column into Datetime ?
I tried so many ways using cast and convert functions like cast(dateofrecord,datetime) or like convert(datetime,replace(DateofRecord,'"','''')) ..it didnt worked..
View 9 Replies
View Related
Jul 20, 2005
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 5 Replies
View Related
Aug 9, 2006
Hi,
I need to get Sort numerically from an alphanumeric column
Regards,
Kihsore
View 3 Replies
View Related
Feb 11, 2015
For a new project. I need to create random alphanumeric characters as primary key values when inserting a record.
eg: cmSbXsFE3l8
It can start from 4 digit characters and can grow to 6, 7 as required
The database will involve more than 10000 concurrent users.
I don't want to use guid or auto increment integer or sequence.
View 9 Replies
View Related