Extracting Patterns from a String 1

[edmundo]

Assume I have a 2 column table.

AlrmCode,AlrmText

AlrmText actually contains data that I need to display as different rows. An example of the information that is kept in the table is:

AlrmCode,AlrmText
2154,*alarm info 1 *alarm info 2 *alarm info3
2178,*alarm info 4 *alarm info 5

But I actually want to create a query to display this as
2154,*alarm info 1
2154,*alarm info 2
2154,*alarm info 3
2178,*alarm info 4
2178,*alarm info 5


Fortunately, each data element to be extracted is separated by an asterix (*) which should make it a bit easier, but I'm a bit stumped as to how to go about it. I've obviously thought about using CharIndex but is this the best method?

Any suggestions would be much appreciated..

thanks...

 

[ccuster]

Hi Edmundo,

I know you were looking for a solution other than the use of CharIndex, but here is one with CharIndex. I tested it and it works on my machine. Let me know if this helps any.

Chuck


SET NOCOUNT ON

CREATE TABLE #TEMP
(ALARMID INT NULL,
POS INT NULL)

CREATE CLUSTERED INDEX IX_TEMP
ON #TEMP
(ALARMID,
POS) --Keep AlarmID and Pos in Order


DECLARE @POS INT, --POSTION OF POINTER IN ALARMTXT
@MAXLEN INT, --MAX LEN OF ALARMTXT
@ALARMID1 INT, --ID OF RECORD ON HAND
@ALARMID2 INT, --ID OF NEXT RECORD TO GRAB
@POS1 INT, --POSITION OF POINTER IN ALARMTXT FOR REC ON HAND
@POS2 INT, --POSITION OF POINT IN ALARMTXT FOR NEXT RECORD
@ALARMTXT VARCHAR(255), --SET UP SINCE SUBQUERIES NOT ALLOWED IN SUBSTRING
@ROW INT --KEEPS TRACK OF WHAT ROW YOU ARE ON

SELECT @MAXLEN = MAX(LEN(ALARMTXT)) FROM ALARM --Make sure you dont go past len of alarmtxt
SELECT @POS=0

WHILE @POS<@MAXLEN
BEGIN
INSERT INTO #TEMP
SELECT ALARM.ALARMID, CHARINDEX('*',ALARM.ALARMTXT,@POS) AS POS
FROM ALARM
WHERE NOT EXISTS (SELECT * FROM #TEMP WHERE #TEMP.ALARMID=ALARM.ALARMID AND #TEMP.POS=CHARINDEX('*',ALARM.ALARMTXT,@POS))
--not to add duplicates
GROUP BY ALARM.ALARMID,ALARM.ALARMTXT
HAVING CHARINDEX('*',ALARM.ALARMTXT,@POS)>0
SELECT @POS=@POS+1
END

SELECT * FROM #TEMP --JUST SHOWING THE TABLE WITH IDS AND POSITIONS OF *'S

DECLARE TEMPPOS CURSOR --SETTING UP CURSOR TO SCROLL THROUGH POSTIONS
LOCAL
SCROLL
STATIC
FOR
SELECT * FROM #TEMP ORDER BY ALARMID, POS --DON'T NEED ORDERBY DUE TO CLUSTER, BUT 2 OPTIONS

SELECT @ROW = 0

OPEN TEMPPOS
FETCH NEXT FROM TEMPPOS INTO @ALARMID1, @POS1
SELECT @ROW=@ROW+1
FETCH NEXT FROM TEMPPOS INTO @ALARMID2, @POS2
SELECT @ROW=@ROW+1
SELECT @ALARMTXT = ALARMTXT FROM ALARM WHERE ALARM.ALARMID=@ALARMID1

IF @ALARMID1=@ALARMID2

BEGIN
PRINT 'ALARMID: '+STR(@ALARMID1,4,0) + ', ALARMTXT: '+ SUBSTRING((@ALARMTXT),@POS1,@POS2-@POS1)
END
ELSE
BEGIN
SELECT @POS2 = LEN(MAX(ALARMTXT)) FROM ALARM WHERE ALARM.ALARMID=@ALARMID1
PRINT 'ALARMID: '+STR(@ALARMID1,4,0) + ', ALARMTXT: '+ SUBSTRING((@ALARMTXT),@POS1,@POS2)
END


WHILE @@FETCH_STATUS=0
BEGIN
FETCH ABSOLUTE @ROW FROM TEMPPOS INTO @ALARMID1, @POS1
FETCH NEXT FROM TEMPPOS INTO @ALARMID2, @POS2

SELECT @ROW=@ROW+1
SELECT @ALARMTXT = ALARMTXT FROM ALARM WHERE ALARM.ALARMID=@ALARMID1
IF @ALARMID1=@ALARMID2 AND @@FETCH_STATUS=0 --SEPARATING LAST ALARMIDS
BEGIN
PRINT 'ALARMID: '+STR(@ALARMID1,4,0) + ', ALARMTXT: '+ SUBSTRING((@ALARMTXT),@POS1,@POS2-@POS1)
END
ELSE
BEGIN
SELECT @POS2 = LEN(MAX(ALARMTXT)) FROM ALARM WHERE ALARM.ALARMID=@ALARMID1
PRINT 'ALARMID: '+STR(@ALARMID1,4,0) + ', ALARMTXT: '+ SUBSTRING((@ALARMTXT),@POS1,@POS2)
END
END

--CLEANUP

DROP TABLE #TEMP
CLOSE TEMPPOS
DEALLOCATE TEMPPOS

 

[edmundo]

thanks chuck, you're a legend...

I obviously have to make a couple of changes to suit my particular database but it worked a treat..

eds