Comparing Fields - 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

Comparing Fields

Author

Topic

TankCR
Starting Member

3 Posts

Posted - 2013-09-20 : 16:12:21

Hi All, I am trying to compare values from two colums but I don't really know what I am doing, I tried

select custom5,skipcount substr( custom5, 1, 4) as 'mynumber' from songs where custom5 > '0000%' where skipcount > cast( substr( custom5, 1, 4) as int );

But that is obviously wrong, can anyone help?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-20 : 16:24:18

quote:
Originally posted by TankCR

Hi All, I am trying to compare values from two colums but I don't really know what I am doing, I tried
select custom5,skipcount substr( custom5, 1, 4) as 'mynumber' from songs where custom5 > '0000%' where skipcount > cast( substr( custom5, 1, 4) as int );

But that is obviously wrong, can anyone help?

There are some syntax errors, which I have fixed below. But I don't know if the logic is correct because I don't know your business requirements.

select custom5,skipcount,
	substring( custom5, 1, 4) as 'mynumber' 
from songs 
WHERE 
	custom5 > '0000%' 
	and skipcount > cast( substring( custom5, 1, 4) as int );

TankCR
Starting Member

3 Posts

Posted - 2013-09-20 : 16:51:53

Perfect, now how do I take the int of '0000' and write it to SkipCount?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-20 : 17:18:58

quote:
Originally posted by TankCR

Perfect, now how do I take the int of '0000' and write it to SkipCount?

I am not clear what you are asking. If you want to convert '0000' to int, you can simply cast it. CAST('0000' AS INT) would do it.

Perhaps the following is what you are looking for? But be aware that if you run the query below, it will permanently change the data in the skipcount column, so test it in a development environment to make sure that is what you want.

UPDATE songs SET
	skipcount = substring( custom5, 1, 4)
from songs 
WHERE 
	custom5 > '0000%' 
	and skipcount > cast( substring( custom5, 1, 4) as int );

TankCR
Starting Member

3 Posts

Posted - 2013-09-20 : 17:48:27

Well one problem it would appear is that the Syntex of SQLite is much different than MS SQL I think what I need looks closer to but I can't seem to get it right

INSERT OR REPLACE INTO SONGS (skipcount) Values (SELECT substr( custom5, 1, 4) FROM SONGS WHERE custom5 > '0000%' and skipcount < cast( substr( custom5, 1, 4) as int ));

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources