Text to int - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Text to int

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2015-04-24 : 13:10:29

I have a field with a datatype of text. In SQL it looks like:

Body(text,null)

This field is used for notes.
For certain records users will enter a date.

20150425

I'm looking to do a select statement that will grab only those records with a date and make them an int field.

This doesn't seem to work.
select convert(int, body)
from b
where left(body,2)='20'

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-24 : 13:21:25

What doesn't work? This worked for me:


create table b (body varchar(20))
insert into b
select 'one'
union
select '20150420'

select * from b

select convert(int, body)
from b
where left(body,2)='20'

It returned:


(No column name)
20150420

Vack
Aged Yak Warrior

530 Posts

Posted - 2015-04-24 : 13:24:49

The field type is not varchar it is text

Vack
Aged Yak Warrior

530 Posts

Posted - 2015-04-24 : 13:30:18

This seems to be working:

select convert(int,convert(varchar(max), body)) as Depositnumber
from BacoDiscussions
where left(convert(varchar(max), body),2)='20'

Kristen
Test

22859 Posts

Posted - 2015-04-25 : 07:08:48

quote:
Originally posted by Vack

The field type is not varchar it is text

We have modified all our databases to change TEXT datatype to VARCHAR(MAX) because things like this "just work" with varchar(MAX), and in other respects VARCHAR(MAX) works the same as TEXT.

Depends what version of SQL you are using though, but (I think??) TEXT is deprecated in current versions of SQL so in order to upgrade you will have to change the columns' datatype anyway.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources