# and text into char or varchar data type

Transact-SQL
1

Hello,

How to load number and text into a char or varchar datatype ?

How is a field or datatype store both text and number because sometimes you would have a situation where both is needed. Please advise !

2

That smells of bad data design - either something is a number or it isn't. It might be something that looks like a number, but isn't, such as a telephone number in which case it should be stored as text. Alternatively it might be that you really have multiple co-related fields and you're trying to cram them into one rather than storing them separately.

Without knowing exactly what you're trying to do, it's hard to advise on what the best solution is.

1 Like
3

Is just an ID field or an identifier field, it can either be a text or a number. Text are alphanumeric and number is just number. I think there are cases where that can happen and shouldn’t be a bad design.

I created with either a varchar or char datatype now I can’t load the numbers in but the text are fine. Can we convert the number and load it as text. I think is similar situation to inserting a blank into a number data type and I seen many database that when there is no number is blank. What do you advise on how I can insert number into a char or varchar ?

4

I didn't quite get why you are not able to insert numeric values in a varchar column. See the example below where I am successfully inserting a number and a string into a table with a varchar column.

drop table if exists #tmp;
create table #tmp (id varchar(32));

insert into #tmp values (1);
insert into #tmp values ('abcd');

select * from #tmp;

If you try to do the following, it will fail.

insert into #tmp 
values
	(2),
	('xyz');

The message would be something like "Conversion failed when converting the varchar value 'xyz' to data type int.". This is due to implicit conversion. When you mix items of more than one datatype into the same values clause, SQL Server internally converts all the items to the same data type - to the one with the highest precedent, which in this case happens to be the numeric value. You can see the precedence order here.

In this case, your choices are, either segregate all the numbers into one insert statement/data load. Alternatively, you can convert everything to varchar as shown below

insert into #tmp 
values
	(cast(2 as varchar(32))),
	(cast('xyz' as varchar(32)));

There is no need for the cast for the 'xyz', but that makes it uniform - i.e., you are converting everything the same way regardless of whether it is numeric, or character string.

5

JamesK,

You are correct I could insert it. I just found out now. It was another issue. Is when I tag a field as a numeric data type that is 23, 19.

But a number I am inserting has about 18 digits or more after the decimal point and I couldn’t insert it into the decimal field. I get an error.

How do we insert a very big decimal number like 20 after the decimal in sql server decimal data type ?

6

If your data type is numeric(23,19), it can store at most 19 decimal digits, and there can be at most 23-19-1=3 digits to the left of the decimal point. If you have 18 digits or more after the decimal point, you should determine what the maximum number of digits you want to store is and set the data type accordingly. See this example:

DROP TABLE IF EXISTS #tmp;
CREATE TABLE #tmp(x DECIMAL (36,25));
INSERT INTO #tmp VALUES ( CAST ('1.234567890123456789012345' AS DECIMAL(36,25)))
SELECT * FROM #tmp;
7

SOL variant has been available since Sql
Server 2000.. Maybe this is a use case.

8

Yes thank you to you both for your advice and information.