How to convert string to "data type" ?

Question

Post reply

How to convert string to "data type" ?

jeremie 98561

Right there with Babe

Points: 718
More actions
May 5, 2017 at 7:10 am

#402031

Hello everybody,
First of all sorry for my English 😉
Here is my problem.
In a table I've :
- MyValue : column (varchar) wich store a value
- MyType : column (varchar) wich store the type of MyValue, but in string format
For example for one row I've :
- MyValue : 25
- MyType : int
SELECT CONVERT(MyType, MyValue) FROM MyTable
How could I make this query ?
Thank you in advance

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 1

Use dynamic query....as you like

EXEC ('Select CONVERT('+MyType+','+MyValue+') into #temp from MyTable')

Use case statements statements with example given...U can change as you needed...

Select CASE WHEN MyType='INT' THEN CONVERT(INT,MyValue)
WHEN MyType='CHAR' THEN CONVERT(CHAR(50),MyValue)
WHEN MyType='BIT' THEN CONVERT(BIT,MyValue)
ELSE NULL END

FROM MYTable

Thanks,
Sasidhar P

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

My advice, if you have any control over this, is to avoid this kind of design. You've seen how difficult it is to write even the simplest query. And what happens when someone inserts a row such as ('A', 'int')?

John

jeremie 98561 Right there with Babe Points: 718 More actions · Answer 3

jeremie 98561

Right there with Babe

Points: 718

May 5, 2017 at 7:26 am

#1941288

Thank you so much !
So quick 😉

jeremie 98561 Right there with Babe Points: 718 More actions · Answer 4

John Mitchell-245523 - Friday, May 5, 2017 7:24 AM
My advice, if you have any control over this, is to avoid this kind of design. You've seen how difficult it is to write even the simplest query. And what happens when someone inserts a row such as ('A', 'int')?
John

A perfect check will be make. No problem about it.

jeremie 98561 Right there with Babe Points: 718 More actions · Answer 5

Pulivarthi Sasidhar - Friday, May 5, 2017 7:19 AM
Use dynamic query....as you like
EXEC ('Select CONVERT('+MyType+','+MyValue+') into #temp from MyTable')
Use case statements statements with example given...U can change as you needed...
Select CASE WHEN MyType='INT' THEN CONVERT(INT,MyValue)
WHEN MyType='CHAR' THEN CONVERT(CHAR(50),MyValue)
WHEN MyType='BIT' THEN CONVERT(BIT,MyValue)
ELSE NULL END
FROM MYTable
Thanks,
Sasidhar P

Could I use the second solution in function ?

Thom A SSC Guru Points: 99129 More actions · Answer 6

Are you expecting to return more than one value back at a time? A simple dataset like this is something to cause you problem if so:
MyType MyValue ------ ------- char(1) A tinyint 1
Using something like Pulivarthi's example is going to give you a conversion error. For example:
CREATE TABLE #Sample (MyType varchar(50), MyValue varchar(50)); GO

INSERT INTO #Sample VALUES ('char(1)','A'), ('tinyint', '1'); GO

SELECT CASE MyType WHEN 'tinyint' THEN CONVERT(tinyint, MyValue) WHEN 'char(1)' THEN CONVERT(char(1), MyValue)
ELSE 'Unknown DataType' END AS MyValue
FROM #Sample;
GO

DROP TABLE #Sample;
GO
This will return:
Msg 245, Level 16, State 1, Line 12
Conversion failed when converting the varchar value 'A' to data type tinyint.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

jeremie 98561 Right there with Babe Points: 718 More actions · Answer 7

jeremie 98561

Right there with Babe

Points: 718

May 5, 2017 at 7:39 am

#1941295

True Thom A !

:-\

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

jeremie 98561 - Friday, May 5, 2017 7:30 AM
A perfect check will be make. No problem about it.

What sort of check?

Thom A - Friday, May 5, 2017 7:33 AM
Using something like Pulivarthi's example is going to give you a conversion error.

Precisely. And it will just get worse the more things that you think of that you want to do. Use the RDBMS how it's meant to be used - it's a lot easier. Or use a different tool that suits the design that you propose.

John

Thom A SSC Guru Points: 99129 More actions · Answer 9

A better question at this stage is what are you actually trying to achieve? I don't mean the conversion of your values, but where is this information going to be displayed, what environment, what is it for?

Like John said, this isn't an ideal way of storing your data, but perhaps we can point out a better way of storing what you have, which will also give you an answer to your initial question.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Seriously, avoid this kind of design. It smells like an EAV table, and those are so hard to work with and perform terribly. I've seen them far too often, and I have not yet seen a case where there isn't a pile of data errors in it (like the 'tinyint, 'ab' example)

Proper DB design, columns storing one piece of data in, where for every row the column means the same thing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jeremie 98561 Right there with Babe Points: 718 More actions · Answer 11

In fact my original problem is:

I have an ARTICLE table and a SPEC table (as specification)
A VAL table (as a value)

VAL.Value is the value of an SPEC of an ITEM

For example, 5 is the "Number of cores" value of the "Intel processor"

I hope to have been clear.

So how to store these values to be able to filter textually, numerically and binary?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 12

jeremie 98561 - Tuesday, May 9, 2017 12:28 AM
I hope to have been clear.

Not really. It's much easier to understand if you post CREATE TABLE and INSERT statements. But it looks as if not only do you have an EAV model, but you also have it unnecessarily split over three tables. If you have any control over this, I (once again) strongly recommend that you use a normalised design instead.

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

jeremie 98561 - Tuesday, May 9, 2017 12:28 AM
In fact my original problem is:
I have an ARTICLE table and a SPEC table (as specification)
A VAL table (as a value)
VAL.Value is the value of an SPEC of an ITEM
For example, 5 is the "Number of cores" value of the "Intel processor"
I hope to have been clear.
So how to store these values to be able to filter textually, numerically and binary?

Time for some reading. What you are trying to do is well understood, there's no need to (attempt to) reinvent the wheel. Here's a reasonable start.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 14

jeremie 98561 - Friday, May 5, 2017 7:10 AM
Hello everybody,
First of all sorry for my English 😉
Here is my problem.
In a table I've :
- MyValue : column (varchar) wich store a value
- MyType : column (varchar) wich store the type of MyValue, but in string format
For example for one row I've :
- MyValue : 25
- MyType : int
SELECT CONVERT(MyType, MyValue) FROM MyTable
How could I make this query ?
Thank you in advance

Google the design flaw known as Entity-Attribute-Value (EAV) and then do not do it.

Please post DDL and follow ANSI/ISO standards when asking for help.