Instr() Equivalent in SQL Server

[DTB]

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.

[Anith Sen]

You can use SUBSTRING in T-SQL 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 )

[David Burke]

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!

[Anith Sen]

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 re-write this using LEFT,
RIGHT etc, please post back.

--
- Anith
( Please reply to newsgroups only )

[David Burke]

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!

[umeshchander]

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 re-write this using LEFT,
RIGHT etc, please post back.

--
- Anith
( Please reply to newsgroups only )