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

How To Sort Table In Sql2000 With Ipaddress(format X.x.x.x) As Column With Nvarchar Datatype In Ascending Order

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

SQL Server 2012 :: Parameter Datatypes Linked To Underlying Table Column Datatype

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

SQL Server 2012 :: Auto Incrementing Alphanumeric Field In Table

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

SQL Server 2012 :: Remove CR-LF In Between Column Which Is Text Datatype?

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

SQL Server 2012 :: More Than 1 Alphanumeric Chars In A String

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

SQL Server 2012 :: Alphanumeric Range Grouping?

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

Modify Nvarchar Datatype To Datatime Datatype

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

Problem With Varchar And Nvarchar Datatype In Linked Server

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

Text Datatype Vs Nvarchar Datatype

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

Sorting Nvarchar Fields

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

Sorting Column For Table - Not Alphabetical Or ID

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

SQL Server 2012 :: Subtract / Exclude Value Items From A Column And Add It To Another Column In Same Table

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

SQL Server 2012 :: Format Value Of A Column And Insert It Into Another Column Of The Same Table?

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

Problem With Sorting The Column Data On Table

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

What Is The ADO DataType For NVarchAR(max)

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

Pass NVARCHAR (or Other String-type) Var As A Column Or Table Name In SQL

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

How To Get DataType Of Column In Table ?

Jul 3, 2004

I want get DataType of Column in Table

ex: nvarchar, varchar, etc...

Help me !!

View 2 Replies View Related

Error With Using Nvarchar Datatype

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

Datatype Nvarchar(max) Not Accessible

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

SQL Server 2012 :: Update Table From Variable Table Column?

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

Find Column Name And Datatype Of A Given Table

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

Converting From Nvarchar To Money Datatype

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

NVARCHAR Vs VARCHAR Datatype And Performance

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

How To Convert Nvarchar Datatype To Float

May 16, 2008

Hi,

how to convert nvarchar datatype to float?

Regards
Prashant

View 10 Replies View Related

Alphanumeric Id Column

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

How To Change The Datatype Of A Column Without Dropping The Table?

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

Conversion Failed When Converting The Nvarchar Value To Datatype Int

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

SQL Server 2012 :: How To Update One Column Value To Another In Same Table

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

SQL Server 2012 :: Converting Nvarchar To Datetime

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

Implicit Conversion Of Datatype Text To Nvarchar Is Not Allowed.

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

Sort Numerically From An Alphanumeric Column

Aug 9, 2006

Hi,

I need to get Sort numerically from an alphanumeric column

Regards,
Kihsore

View 3 Replies View Related

SQL 2012 :: Create Random Alphanumeric Characters For Primary Key Values

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







Copyrights 2005-15 www.BigResource.com, All rights reserved