How To Separate Int And String Values In A Table?

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

SQL

Hi,
     I have a  table named Itemdetails. There is a column named ItemCode it is initialised as varchar(50). So I entered both int and string values to the column. Now I need to use auto increment operation for the integer values in the column. So I need to get  max(ItemCode)+1 value. For that I need to seperate string and int values.
Thanks

Posted 30-Mar-14 23:44pm

My Doubt

Add a Solution

Comments

Aarti Meswania 31-Mar-14 9:10am

can you give format of Itemcode ?
like
item_001 or item001...

1 solution

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)

OriginalGriff · Accepted Answer · 2014-03-31T00:58:00

Solution 1

You can't.
A VARCHAR column does not contain integers - it only contains strings, so all the "Integer values" you inserted were converted to strings by SQL Server when you issued the INSERT command.

It might be possible to write some SQL to convert them back to integer, increment them, and UPDATE the column, but it wouldn't be that simple as SQL doesn't have a "TryParse" method which allows you to check if a column would convert - you would have to rely on SQL exceptions and catch those. Nasty stuff.

Instead, I would add a new INT column, allow NULL values on your VARCHAR column, and knock up some C# / VB code to look at each row in turn. If it is numeric, convert it to a number, write the number to the new numeric column, and NULL the text. Tehn modify your existing software to work with the two columns. It'll be a lot, lot quicker and easier in the long run.

Posted 31-Mar-14 0:58am

OriginalGriff

Comments

My Doubt 31-Mar-14 7:13am

I need to get the max id of the column. It says that "Conversion failed when converting the varchar value 'abcd' to data type int".
But I need to find the max id of the column for using auto increment.
Please help me out.

OriginalGriff 31-Mar-14 7:38am

That is exactly the problem: there is no "try and convert this" method: so SQL will throw an exception.
You really, really, need to change the design.
And unless you *really* know what you are doing, use an Identity field instead of working it out yourself - it's very risky when you try to use this in a multiuser system!

My Doubt 31-Mar-14 7:45am

Hi OriginalGriff,
I can't able to change the design because that table is already used by others. I got a new requirement that Item Code should be having 2 forms either auto increment id or by user entry. The user can enter any type of data like abc,abc123. But the auto incremented value will in integers like 345,346,347 etc.

OriginalGriff 31-Mar-14 8:33am

Then you need to go to whoever made the requirement and explain the situation.
Someone has made a poor design decision, and the best way is to rectify it, or change the requirement to something that fits with the existing design.

My Doubt 1-Apr-14 0:27am

Hi OriginalGriff,
The main situation is that I have an itemcode which can be entered by users or a number generated by the system. In my design there is a text box named Item code along with a button. On the text box user can directly enter the item code (it will be a string or an integer) and on clicking the button the item code is automatically generated( should be an integeger and cannot be duplicated).
Thanks

OriginalGriff 1-Apr-14 4:40am

All the more reason to have two separate columns.
What happens if the user types it wrong?
The "actual" item code should be unique: an integer Identity is ok, but I prefer Guids.
YOu can add a "user entered" string version (such as a part code) as well, but don't try to mix-n-match them!

My Doubt 1-Apr-14 4:46am

Currently I am using with Max(ItemID)+1 to the ItemCode. Here there will have a conflict on duplicate. If a duplication occurs I will show a message and user can enter other code.
Any way Thanks OriginalGriff for spending your valuable time with me.