x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

SUBSTRING with Delimiters

I've been having to parse DB2 Load Cards and to extract a lot of data (Like starting postion, Length, Column name, ect) you need parse the data out of the card based on delimiters.  DB2 pretty much is consistent as to were the data resides in it's offsets to certain keywords.  Anyway instead of using strat and Length, I figured I could make a substring function that uses “delimiters”

CREATE FUNCTION SUBSTRING_DEL(
 @Col varchar(8000), @s varchar(1000), @e varchar(1000)
)
RETURNS varchar(8000)
AS
  BEGIN
 DECLARE @rs varchar(8000)
 SELECT @rs = SUBSTRING( @Col
     , CHARINDEX(@s,@col)+LEN(@s)
     , ((CHARINDEX(@e,@Col,CHARINDEX(@s,@Col)+LEN(@s))) - (CHARINDEX(@s,@Col)+LEN(@s))))
 Return LTRIM(RTRIM(@rs))
  END
GO

Here's a sample line as to what a line in the DB2 Control Card looks like

DECLARE @x varchar(80)
SELECT  @x = ' PERSON_ID                              POSITION(       1         )'
SELECT    LTRIM(RTRIM(SUBSTRING(@x,2,18)))     AS COLUMN_NAME
 , CONVERT(int,dbo.SUBSTRING_DEL(@x,'POSITION(',')')) AS StartingPosition

 

EDIT: I'm trying to change this

RowNum      Mnemonic Card                                                                            
----------- -------- --------------------------------------------------------------------------------
1           T0000090   LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE                                   
2           T0000090       AXBB72DA.PERS_TELE_ALL                                                   
3           T0000090    (                                                                           
4           T0000090    PERSON_ID                              POSITION(       1         )          
5           T0000090    CHAR(                     12) ,                                             
6           T0000090    ID_TYPE_CD                             POSITION(      13         )          
7           T0000090    CHAR(                      3) ,                                             
8           T0000090    ASSOC_TYPE_CD                          POSITION(      16         )          
9           T0000090    CHAR(                      5) ,                                             
10          T0000090    STATUS_CD                              POSITION(      21         )          
11          T0000090    CHAR(                      1) ,                                             
12          T0000090    LAST_NAME                              POSITION(      22         )
          
13          T0000090    CHAR(                     20) ,                                     

 

Into this

 

rowNum      Mnemonic COLUMN_NAME        StartingPosition Length     
----------- -------- ------------------ ---------------- -----------
4           T0000090 PERSON_ID          1                12
6           T0000090 ID_TYPE_CD         13               3
8           T0000090 ASSOC_TYPE_CD      16               5
10          T0000090 STATUS_CD          21               1
12          T0000090 LAST_NAME          22               20

 

And was done using

SELECT a.rowNum, a.Mnemonic, a.COLUMN_NAME, a.StartingPosition, b.Length
FROM (
 SELECT    RowNum, Mnemonic
  , LTRIM(RTRIM(SUBSTRING(l.Card,2,18)))     AS COLUMN_NAME
  , CONVERT(int,dbo.SUBSTRING_DEL(l.Card,'POSITION(',')')) AS StartingPosition
   FROM CTLCards l
  WHERE l.Card LIKE '%POSITION%'
) AS a
JOIN (
 SELECT    r.RowNum, l.Mnemonic
  , LTRIM(RTRIM(SUBSTRING(r.Card,2,18)))    AS COLUMN_NAME
  , CONVERT(int,dbo.SUBSTRING_DEL(l.Card,'(',')')) AS Length
   FROM CTLCards l JOIN CTLCards r ON l.RowNum = (r.RowNum + 1)
  WHERE l.Card LIKE '%CHAR(%'
) AS b
ON a.RowNum = b.RowNum

 

Print | posted on Wednesday, June 08, 2005 12:32 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: SUBSTRING with Delimiters

Hi All,

I have like
ink-i
pen-p
blue
red
yellow-i
green-p
brown

i need to select only the records which are p and i .
i will give logic.
frist i have to go upto last character and , i have to check second last shpuld be - and finally i have to pick the i and p .
can any one write sql for these !
no pl/sql plz
8/21/2006 2:57 PM | cndraa
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET