CHARINDEX in SQL Statement - 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

SQL Server 2012 Forums

Transact-SQL (2012)

CHARINDEX in SQL Statement

Author

Topic

SQLNoob81
Starting Member

38 Posts

Posted - 2013-04-16 : 06:14:32

Hi All.

I would really appreciate some help with a statement that I need to make some changes to a table.

I have a table called Applicants and a field called SalaryRange.

a typical value in SalaryRange would be '5000to7000'.

I need to first of all remove the 5000to part and multiple the 7000 X 12 to get an annual salary range.

is this possible?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-16 : 06:47:15

--This??
SELECT CAST(REPLACE(SalaryRange, LEFT(SalaryRange, charindex('to', SalaryRange)+1), '') AS INT)*12 AnnSalRange
FROM Applicants

-- Check example
DECLARE @SalaryRange VARCHAR(20) = '5000to7000'
SELECT CAST(REPLACE(@SalaryRange, LEFT(@SalaryRange, charindex('to', @salaryRange)+1), '') AS INT)*12

--
Chandu

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-04-16 : 07:06:53

or this?
declare @str varchar(50) = '5000to7000'
select SUBSTRING(@str,CHARINDEX('to',@str)+2,50) *12

Jim

Everyday I learn something that somebody else already knew

SQLNoob81
Starting Member

38 Posts

Posted - 2013-04-16 : 07:48:32

Thanks guys, I forgot to mention that I would need to update the field with the new value. Sorry for the inconvenience!

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-16 : 07:55:21

Do you mean to update/replace the values of same field where it stores data like '5000to7000'?

Cheers
MIK

SQLNoob81
Starting Member

38 Posts

Posted - 2013-04-16 : 08:09:48

Yes, that what I meant. So 5000to7000 would be replaced with 84000

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-16 : 08:38:32

UPDATE Applicants
SET SalaryRange = CAST(REPLACE(SalaryRange, LEFT(SalaryRange, charindex('to', SalaryRange)+1), '') AS INT)*12

-- simple way is:
UPDATE Applicants
SET SalaryRange = 84000
where SalaryRange = '5000to7000'

--
Chandu

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-04-16 : 08:56:25

Also be careful, '5000to7000' is a string, which you are replacing with an int. You are converting a string to an int and then back to a string again

UPDATE Applicants
SET SalaryRange = CONVERT(varchar(xx),SUBSTRING(SalaryRange,CHARINDEX('to',SalaryRange)+2,50) *12)

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources