P: n/a

I am trying to convert a complex function from Oracle to SQL Server
and have come across Oracle's Instr() function. I see SQL Server has
CHARINDEX() which is similar, however it does not provide some key
functionality I need. Here is an example of the Oracle code:
if Instr( sTg , cDelim, 1, 3 ) > 0 then
sd := SubStr( sTg, Instr( sTg , cDelim, 1, 1 ) + 1, Instr( sTg,
cDelim, 1, 2 )  Instr( sTg , cDelim, 1, 1 )  1)
end if;
Has anybody converted anything similar to this within SQL Server? Any
help is GREATLY appreciated!
Thanks.  
Share this Question
P: n/a

You can use SUBSTRING in TSQL as well. Check out CHARINDEX & SUBSTRING
functions in SQL Server Books Online. If you need specific help, please post
an example string, with a brief explanation of the logic to be applied along
with the expected result.

 Anith
( Please reply to newsgroups only )  
P: n/a

Thank you, Here is a more detailed example of the code I need to
convert. The combo if SUBSTRING and CHARINDEX are not working
sufficiently enough with this scenario.
varchar2 sTg := '01/01/1999'
char cDelim := '/'
if Instr( sTg , cDelim, 1, 3 ) > 0 then
 if there are more than two, return null
return null;
elsif Instr( sTg , cDelim, 1, 2 ) > 0 then
 else parse the date
sMonth := SubStr( sTg, 1, Instr( sTg , cDelim, 1, 1 )  1 );
sDay := SubStr( sTg, Instr( sTg , cDelim, 1, 1 ) + 1, Instr(
sTg , cDelim, 1, 2 )  Instr( sTg , cDelim, 1, 1 )  1);
sYear := SubStr( sTg, Instr( sTg , cDelim, 1, 2 ) + 1);
elsif Instr( sTg , cDelim, 1, 1 ) > 0 then
sMonth := SubStr( sTg, 1, Instr( sTg , cDelim, 1, 1 )  1 );
sYear := SubStr( sTg, Instr( sTg , cDelim, 1, 1 ) + 1);
sDay := '';
else
return null;
end if;
The expected result would be to parse out the month, day and year. The
string may come in as any of the following: 10/01/1999, 10/1999,
10.01.99, 10 01 99, etc...
Thanks.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!  
P: n/a

You can do this in several ways. The most common ones are using LEFT, RIGHT
functions. However, since I initially suggested you use SUBSTRING &
CHARINDEX, here is a solution using only those functions:
DECLARE @dt VARCHAR(15), @delim CHAR(1)
SET @dt = '10/09/1999'
SET @delim = '/'
IF LEN(@dt)  LEN(REPLACE(@dt, @delim, SPACE(0))) = 2
SELECT SUBSTRING(@dt, 1, CHARINDEX(@delim, @dt)  1),
SUBSTRING(@dt, CHARINDEX(@delim, @dt) + 1,
CHARINDEX(@delim, @dt,
CHARINDEX(@delim, @dt) + 1) 
CHARINDEX(@delim, @dt)  1) ,
SUBSTRING(@dt, LEN(@dt)+ 1 
CHARINDEX(@delim, REVERSE(@dt)) + 1, LEN(@dt))
ELSE IF LEN(@dt)  LEN(REPLACE(@dt, @delim, SPACE(0))) = 1
SELECT SUBSTRING(@dt, 1, CHARINDEX(@delim, @dt)  1),
SUBSTRING(@dt, LEN(@dt)+ 1 
CHARINDEX(@delim, REVERSE(@dt)) + 1, LEN(@dt))
ELSE
SELECT @dt
Note that you can use CASE expressions, if you need it as a single SQL
statement. As mentioned before, if you need to rewrite this using LEFT,
RIGHT etc, please post back.

 Anith
( Please reply to newsgroups only )  
P: n/a

Thanks Anith! I will try this and see if it gives me what I need.
Thank you!
David
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!  
P: 1

HI Anith,
I am new to this SQL so could u please also resolve one of my problem.
Could u please change the following oracle code in SQL so that i can proceed with other functionality.
Hope the group will help me out.
start_pos = INSTR(ver, '.', 1, 1) 1;
end_pos = INSTR(ver, '.', 1, 2) +3;
ver_core = RTRIM(SUBSTR(ver, start_pos, end_pos  start_pos), CHR(10));
ver_ctry = SUBSTR(ver, start_pos, end_pos  start_pos);
ver := ver_core  '/'  ver_ctry  '/'  ver_trans;
You can do this in several ways. The most common ones are using LEFT, RIGHT
functions. However, since I initially suggested you use SUBSTRING &
CHARINDEX, here is a solution using only those functions:
DECLARE @dt VARCHAR(15), @delim CHAR(1)
SET @dt = '10/09/1999'
SET @delim = '/'
IF LEN(@dt)  LEN(REPLACE(@dt, @delim, SPACE(0))) = 2
SELECT SUBSTRING(@dt, 1, CHARINDEX(@delim, @dt)  1),
SUBSTRING(@dt, CHARINDEX(@delim, @dt) + 1,
CHARINDEX(@delim, @dt,
CHARINDEX(@delim, @dt) + 1) 
CHARINDEX(@delim, @dt)  1) ,
SUBSTRING(@dt, LEN(@dt)+ 1 
CHARINDEX(@delim, REVERSE(@dt)) + 1, LEN(@dt))
ELSE IF LEN(@dt)  LEN(REPLACE(@dt, @delim, SPACE(0))) = 1
SELECT SUBSTRING(@dt, 1, CHARINDEX(@delim, @dt)  1),
SUBSTRING(@dt, LEN(@dt)+ 1 
CHARINDEX(@delim, REVERSE(@dt)) + 1, LEN(@dt))
ELSE
SELECT @dt
Note that you can use CASE expressions, if you need it as a single SQL
statement. As mentioned before, if you need to rewrite this using LEFT,
RIGHT etc, please post back.

 Anith
( Please reply to newsgroups only )
  This discussion thread is closed Replies have been disabled for this discussion.   Question stats  viewed: 306248
 replies: 5
 date asked: Jul 20 '05
