> Hi,
>
> We've migrated an Oracle table to SQL and old processes calling this table
> also needs conversion.
>
> One such SELECT stmt, shown below, extensively uses the INSTR Oracle string
> function.
>
> SELECT
> SUBSTR(ft_reference, INSTR(ft_reference,''['', INSTR(ft_reference,''CHECK
> #'', 1, 1), 1)+1, (INSTR(ft_reference,'']'', INSTR(ft_reference,''CHECK #'',
> 1, 1), 1) - INSTR(ft_reference,''['', INSTR(ft_reference,''CHECK #'', 1, 1),
> 1)-1)) "CheckNumber"
> FROM GUEST
>
> To make my Friday less stressful, I simply replaced SUBSTR and INSTR with
> T-SQL equivalent, SUBSTRING and CHARINDEX (along with changing the double ""
> to single ''), as in:
>
> SELECT
> SUBSTRING(ft_reference, CHARINDEX(ft_reference,'[',
> CHARINDEX(ft_reference,'CHECK #', 1, 1), 1)+1,(CHARINDEX(ft_reference,']',
> CHARINDEX(ft_reference,'CHECK #', 1, 1), 1) - CHARINDEX(ft_reference,'[',
> CHARINDEX(ft_reference,'CHECK #', 1, 1), 1)-1)) AS 'CheckNumber'
> FROM GUEST
>
> That didn't work... so I tried deciphering it but got lost while I was in
> the second or third CHARINDEX.
>
> I'm no expert in either PL\SQL or T-SQL and would appreciate your help,
> comments, and/or suggestions here. TIA.
>
> Regards

"SQL Menace" wrote:

> Can you show some sample data and what the output should look like?
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
>
>
> Rob wrote:
> > Hi,
> >
> > We've migrated an Oracle table to SQL and old processes calling this table
> > also needs conversion.
> >
> > One such SELECT stmt, shown below, extensively uses the INSTR Oracle string
> > function.
> >
> > SELECT
> > SUBSTR(ft_reference, INSTR(ft_reference,''['', INSTR(ft_reference,''CHECK
> > #'', 1, 1), 1)+1, (INSTR(ft_reference,'']'', INSTR(ft_reference,''CHECK #'',
> > 1, 1), 1) - INSTR(ft_reference,''['', INSTR(ft_reference,''CHECK #'', 1, 1),
> > 1)-1)) "CheckNumber"
> > FROM GUEST
> >
> > To make my Friday less stressful, I simply replaced SUBSTR and INSTR with
> > T-SQL equivalent, SUBSTRING and CHARINDEX (along with changing the double ""
> > to single ''), as in:
> >
> > SELECT
> > SUBSTRING(ft_reference, CHARINDEX(ft_reference,'[',
> > CHARINDEX(ft_reference,'CHECK #', 1, 1), 1)+1,(CHARINDEX(ft_reference,']',
> > CHARINDEX(ft_reference,'CHECK #', 1, 1), 1) - CHARINDEX(ft_reference,'[',
> > CHARINDEX(ft_reference,'CHECK #', 1, 1), 1)-1)) AS 'CheckNumber'
> > FROM GUEST
> >
> > That didn't work... so I tried deciphering it but got lost while I was in
> > the second or third CHARINDEX.
> >
> > I'm no expert in either PL\SQL or T-SQL and would appreciate your help,
> > comments, and/or suggestions here. TIA.
> >
> > Regards
>
>

> When this stmt is run in Oracle, here's what I get:
>
> CheckNumber
> -----------
> 8570
> 8558
> 8558
> 8558
> 8558
>
> The GUEST table contains this info:
>
> CHEQUE_NUMBER
> --------------------
> 855820050416172159
> 855820050416172159
> 857020050416211328
> 857020050416211328
> 857020050416211328
>
> Thanks.
>
> "SQL Menace" wrote:
>
> > Can you show some sample data and what the output should look like?
> >
> > Denis the SQL Menace
> > http://sqlservercode.blogspot.com/
> >
> >
> >
> > Rob wrote:
> > > Hi,
> > >
> > > We've migrated an Oracle table to SQL and old processes calling this table
> > > also needs conversion.
> > >
> > > One such SELECT stmt, shown below, extensively uses the INSTR Oracle string
> > > function.
> > >
> > > SELECT
> > > SUBSTR(ft_reference, INSTR(ft_reference,''['', INSTR(ft_reference,''CHECK
> > > #'', 1, 1), 1)+1, (INSTR(ft_reference,'']'', INSTR(ft_reference,''CHECK #'',
> > > 1, 1), 1) - INSTR(ft_reference,''['', INSTR(ft_reference,''CHECK #'', 1, 1),
> > > 1)-1)) "CheckNumber"
> > > FROM GUEST
> > >
> > > To make my Friday less stressful, I simply replaced SUBSTR and INSTR with
> > > T-SQL equivalent, SUBSTRING and CHARINDEX (along with changing the double ""
> > > to single ''), as in:
> > >
> > > SELECT
> > > SUBSTRING(ft_reference, CHARINDEX(ft_reference,'[',
> > > CHARINDEX(ft_reference,'CHECK #', 1, 1), 1)+1,(CHARINDEX(ft_reference,']',
> > > CHARINDEX(ft_reference,'CHECK #', 1, 1), 1) - CHARINDEX(ft_reference,'[',
> > > CHARINDEX(ft_reference,'CHECK #', 1, 1), 1)-1)) AS 'CheckNumber'
> > > FROM GUEST
> > >
> > > That didn't work... so I tried deciphering it but got lost while I was in
> > > the second or third CHARINDEX.
> > >
> > > I'm no expert in either PL\SQL or T-SQL and would appreciate your help,
> > > comments, and/or suggestions here. TIA.
> > >
> > > Regards
> >
> >