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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
2241 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
2875 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
1054 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
2241 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
2875 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  
 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.09 seconds. Powered By: Snitz Forums 2000