SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 CHARINDEX in SQL Statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLNoob81
Starting Member

South Africa
38 Posts

Posted - 04/16/2013 :  06:14:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 04/16/2013 :  06:47:15  Show Profile  Reply with Quote
--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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 04/16/2013 :  07:06:53  Show Profile  Reply with Quote
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
Go to Top of Page

SQLNoob81
Starting Member

South Africa
38 Posts

Posted - 04/16/2013 :  07:48:32  Show Profile  Reply with Quote
Thanks guys, I forgot to mention that I would need to update the field with the new value. Sorry for the inconvenience!
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/16/2013 :  07:55:21  Show Profile  Reply with Quote
Do you mean to update/replace the values of same field where it stores data like '5000to7000'?

Cheers
MIK
Go to Top of Page

SQLNoob81
Starting Member

South Africa
38 Posts

Posted - 04/16/2013 :  08:09:48  Show Profile  Reply with Quote
Yes, that what I meant. So 5000to7000 would be replaced with 84000
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 04/16/2013 :  08:38:32  Show Profile  Reply with Quote
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

Edited by - bandi on 04/16/2013 08:40:01
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 04/16/2013 :  08:56:25  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000