Turn a character-value into a numeric value/count

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

Turn a character-value into a numeric value/count

Author

Topic

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-08-10 : 10:30:47

Hi,

I am using a cursor (as described in the 'popular articles' on this website) to put the value of a variable into a declared variable called @test:

FETCH NEXT FROM c1
INTO @Test

What I am now trying to do is, find out wheather the variable has the value 'A' or 'B'.

The plan is, to count how often A or B are beeing repeated row by row as in the following example:

ID.....AorB......Repeat
01.....B.........1.....
02.....B.........2.....
03.....B.........3.....
04.....A.........1.....
05.....B.........1.....
06.....B.........2.....
.......................

I am new to sql and don't have a clue how to realize this, it would be great if somebody could show me.

PLEASE HELP,

many thanks!!!!!!!!

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-08-10 : 10:47:07

This was my first try, but it is definitly no SQL ;)

WHILE (SELECT ID FROM Test) < 100
BEGIN
SET @now = AorB
SET @id = ID

IF @before = @now
SET @repeat = @repeat + 1
IF @before <> @now
SET @repeat = 1
IF @before <> @now
SET @before = AorB
UPDATE Test SET Repeat = @repeat
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-10 : 10:54:30

Are you using SQL Server 2000 or 2005 ?
is the ID a continuous running number ?

KH
[spoiler]Time is always against us[/spoiler]

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-08-10 : 11:05:50

Hi,

I am using SQL Server 2005 and the ID is not continuous, it goes like 20,105,121,200 etc

Tnx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-10 : 13:16:43

[code]SELECT t.ID,t.AorB,COALESCE(d.Repeat,0)+1 AS Repeat
FROM YourTable t
OUTER APPLY (SELECT Top 1 ID
FROM YourTable
WHERE AorB <> t.AorB
AND ID > t.ID
ORDER BY ID)b
OUTER APPLY (SELECT Top 1 ID
FROM YourTable
WHERE AorB <> t.AorB
AND ID < t.ID
ORDER BY ID DESC)c
OUTER APPLY (SELECT COUNT(ID) AS Repeat
FROM YourTable
WHERE AorB = t.AorB
AND ID <t.ID
AND ID > COALESCE(c.ID,0)
AND ID < COALESCE(b.ID,99999)
ORDER BY ID)d
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-10 : 14:17:38

http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data

E 12°55'05.25"
N 56°04'39.16"

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-08-10 : 21:15:38

Hi,

tnx for the answer. I unfortunately recieve the following error message:

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'OUTER'.
Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'ORDER'.
Server: Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'ORDER'.
Server: Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'ORDER'.

Many tnx again

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-10 : 22:13:56

check your database compatibility level make sure it is 90

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-10 : 23:48:55

if not change compatibility level using sp_dbcmptlevel
http://msdn.microsoft.com/en-us/library/ms178653.aspx

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-08-11 : 06:10:13

hi, I can't change the level, I don't have the necessary rights (and I am also not able to make anybody change them). is there another possibility?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 06:14:54

Follow the link and read the article?

E 12°55'05.25"
N 56°04'39.16"

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-08-12 : 08:44:18

tnx for the link! this would work just fine (modified <> to =), if we would reset the "Rungroup" Variable to 1 evertime G.Result <> GR.Result. How can we build this in?

SELECT GameDate,
Result,
(SELECT COUNT(*)
FROM GameResults G
WHERE G.Result = GR.Result
AND G.GameDate <= GR.GameDate) as RunGroup
FROM GameResults GR
ORDER BY GameDate

thank you so much!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 08:53:23

[code]DECLARE @Sample TABLE
(
ID VARCHAR(20) PRIMARY KEY CLUSTERED,
AorB CHAR(1),
[Repeat] INT
)

INSERT @Sample
(
ID,
AorB
)
SELECT '01', 'B' UNION ALL
SELECT '02', 'B' UNION ALL
SELECT '03', 'B' UNION ALL
SELECT '04', 'A' UNION ALL
SELECT '05', 'B' UNION ALL
SELECT '06', 'B'

DECLARE @AorB CHAR(1),
@Repeat INT

UPDATE @Sample
SET @Repeat = [Repeat] = CASE
WHEN AorB = @AorB THEN @Repeat + 1
ELSE 1
END,
@AorB = AorB

SELECT *
FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-08-12 : 09:53:17

AWESOME! You rule!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 10:21:46

The key to success here is the clustered index.

E 12°55'05.25"
N 56°04'39.16"

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-08-13 : 04:27:30

Is it possible to loop through several tables, where this calculation needs to be done?? So that during the first loop, I will update Table @Sample, next I will update @Sample2, continuing until @SampleN

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 04:38:08

No.
Why don't you just insert all records into @sample table?
Add a new column denoting source table.
And make the clustered index over the new column first, then as before.

E 12°55'05.25"
N 56°04'39.16"

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-08-13 : 05:56:26

I am not allowed to create tables or columns, I am only allowed to update values in existing tables, that's why I am asking.. I can do this manually, I was just curious if it was possible.. tnx for the answer!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 06:44:04

[code]CREATE PROCEDURE dbo.usp_MyTwitProcedure
(
@TableName SYSNAME,
@CheckColumnName SYSNAME = 'AorB',
@SequenceColumnName SYSNAME = 'Repeat',
)
AS

SET NOCOUNT ON

DECLARE @SQL VACHAR(8000)

SET @SQL = '
DECLARE @AorB CHAR(1),
@Repeat INT

UPDATE ' + QUOTENAME(@TableName) + '
SET @Repeat = ' + QUOTENAME(@SequenceColumnName) + ' = CASE
WHEN ' + QUOTENAME(@CheckColumnName) + ' = @AorB THEN @Repeat + 1
ELSE 1
END,
@AorB = ' + QUOTENAME(@CheckColumnName)

EXEC (@SQL)[/code]Call with

EXEC dbo.usp_MyTwitProcedure 'Table1', 'AorB', 'Repeat'
EXEC dbo.usp_MyTwitProcedure 'Table2'
EXEC dbo.usp_MyTwitProcedure 'Table3', 'Col1', 'Col2'

Make sure there is a proper clustered index on every table.
I take no responsibility for this stored procedure because I don't know yor environment!

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources