Extracting a piece of a string in a field

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 2000 Forums

SQL Server Development (2000)

Extracting a piece of a string in a field

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-08-28 : 13:39:19

Hi. I need to construct a query for my SQL Server 7.0 database based on two fields. My problem is that the only data I need from the second field (CHAR data type, but seven number string like this 0002000 -- don't ask, I didn't design it!) is the value located in the second position of the string. Once I tag this value, I then need to increment it by one (1).

The name of the table is TEST_SCORES, the fields I need are:
STUDENT_ID, SUMMARY_SCORE (summary score is the field from which I need to extract the value in the second position and increment it by 1).

Any help is appreciated.

Thx.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-28 : 14:06:32

SELECT STUDENT_ID, SUMMARY_SCORE AS OLD_SCORE, RIGHT('0000000' + CAST((CAST(SUMMARY_SCORE AS INT) + 1) AS VARCHAR), 7) AS NEW_SCORE FROM TEST_SCORES

use that I think it should work

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-28 : 14:10:17

just something i felt like playing with ...

DECLARE @t TABLE (STUDENT_ID INT, SCORE_SUMMARY CHAR(7))
DECLARE @i INT

SET @i = 0

WHILE @i < 12
BEGIN
INSERT INTO @t (STUDENT_ID, SCORE_SUMMARY) VALUES (@i, RIGHT('0000000' + CAST((@i * 1000) AS VARCHAR), 7))
SET @i = @i + 1
END

UPDATE @t
SET SCORE_SUMMARY = RIGHT('0000000' + CAST((CAST(SCORE_SUMMARY AS INT) + 1) AS VARCHAR), 7)

SELECT * FROM @t

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-08-28 : 14:33:52

Onamuji,

Doesn't your code actuall inrement the entire field by 1 instead of the 2nd character? Or did I misunderstand the question?

I think this will involve some string parsing to extract the 2nd character, increment it, and stuff it back in.

Something like this:

SET NOCOUNT ON
CREATE TABLE #Temp (
RecID int IDENTITY(1,1),
ScoreSummary char(7)
)

INSERT INTO #Temp (ScoreSummary) VALUES ('0002000')
INSERT INTO #Temp (ScoreSummary) VALUES ('1234567')
INSERT INTO #Temp (ScoreSummary) VALUES ('0805486')
INSERT INTO #Temp (ScoreSummary) VALUES ('9999999')
SET NOCOUNT OFF

SELECT RecID, ScoreSummary FROM #Temp

UPDATE #Temp SET
ScoreSummary = substring(ScoreSummary,1,1)
+ CAST((CAST(substring(ScoreSummary,2,1) as int) + 1) as char(1))
+ substring(ScoreSummary, 3,5)

SELECT RecID, ScoreSummary FROM #Temp

DROP TABLE #Temp

Steelkilt, what happens if the digit in that position is 9?

Edited by - ajarnmark on 08/28/2002 14:50:32

Edited by - ajarnmark on 08/28/2002 14:52:42

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-08-28 : 14:54:18

What about this, based on an earlier question that was answered by SQL team:

DECLARE @iNumValue int, @charOldValue char(6)
SELECT @iNumValue = CAST( SUBSTRING(SUMMARY_SCORE, 2, 1) AS Integer ), @charOldValue = SUMMARY_SCORE
FROM TEST_SCORES
SET @iNumValue = @iNumValue + 1

AjarnMark,

Good questions, all. I should have provided more background. First, the max value is 6 for any position in the string. Second, I do not need to return the incremented value to the string, what I need to do is return a table that shows the student_id and a new field called something like TEST_EXTRACT. In the TEST_EXTRACT field would be the single incremented value that results from the work above. This string I've been talking about actually represents values for seven different tests!

More background: this dilemma has presented based on a VB application that interfaces with a VERY poorly designed database. For some reason, the programmer/db designer chose not to write each unique test score to its own field, but decided instead to run them all together in this summary STRING. The incrementing is required because the programmer wrote the actual test score string to the database in a DECREMENTED fashion! Now, when someone asks for a run of only one test score (as happened this morning), we have to jump through these hoops.

In time I will have redesigned the database (and the front end) completely. Right now, I just have to make the existing hunk of junk work.

thanks for all the help!

Edited by - steelkilt on 08/28/2002 14:59:58

Edited by - steelkilt on 08/28/2002 15:03:52

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-28 : 14:54:32

could go either way ... value located in the second position of the string isn't very clear ...

----------------------------------------------------------------------
I'm just more confused now

but if you have it working horrah!

Edited by - onamuji on 08/28/2002 15:05:42

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources