which datatype to be used to store text aswellas number in sql server

Question

0.00/5 (No votes)

See more:

Hi everyone,
One of my tables column contain the following type of data.

Room No
G111
1234
2342

Here the first letter indicates the floor no.

Kindly let me know which datatype to be used to store this kind of data..

I have tried varchar and nvarchar...but both of them not working..

Thank you..

Posted 23-Nov-13 23:18pm

2012programmer

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

agent_kruger · Answer 1 · 2013-11-23T23:30:00

Solution 1

In varchar(MAX) you can save any type of data (text ,number & special characters )

Posted 23-Nov-13 23:30pm

agent_kruger

Comments

2012programmer 24-Nov-13 5:47am

But, the problem here is, I am importing data from an MS-Excel file. I am able to insert the data from sql server mangement studio, but I am unable to import it from excel, bcoz of this data typ issue..

Jawad Ahmed Tanoli 24-Nov-13 5:58am

Befor inserting into sql table check through debug what is the value of Room No in Query..

Jawad Ahmed Tanoli 24-Nov-13 6:10am

to check
set allow null for RoomNo in table may be there is no value of RoomNo in Query.

2012programmer 24-Nov-13 6:23am

the table is already exist, and I am using Import wizard of SQL server management studio, I am able to import it, but If the "roomno." column data is not starting with "G" then it is taking "NULL"(instead of no.1234)..

CHill60 · Answer 2 · 2013-11-24T00:11:00

Note Solution 1 from Harsh Oswal is correct. This is not an alternative solution but additional information in response to subsequent comments

When importing from excel, if the table doesn't already exist, then SQL will attempt to work out what the column type should be based on the first row of data. If that row is numeric then you will hit a problem when you get to rows beginning with 'G'.

Ways around this ...

a) Define the table schema first and import to that table (rather than allowing SQL to create the table during the import)
OR
b) Ensure that the first row in the excel database contains textual data in that column (not recommended)
OR
c) Do data transformations as part of the import - there was a wizard for DTS imports in VS200-2005, there's bound to be one in the later versions.

agent_kruger · Answer 3 · 2013-11-24T14:56:00

Solution 3

if i understood it correctly you can use general datatype in excel

Posted 24-Nov-13 14:56pm

agent_kruger

Comments

2012programmer 25-Nov-13 0:33am

Yeah, I have tried with General datatype and Text data type also...

agent_kruger 25-Nov-13 5:17am

general datatype accepts all type of data inputs please try again?