Hello all,

I'm trying to use the CHARINDEX() function in a view.

Help/BOL tells me that it has an optional 3rd argument for the starting
position:

CHARINDEX ( expression1 ,expression2 [ , start_location ] )
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/78c10341-8373-4b30-b404-3db20e1a3ac4.htm
(I don't know if you can use that URL...if it is for a local file or if it
will take you to an appropriat place on the internet...let me know)

When I try to use the optional 3rd argument...no workie!!!  I get the error
message I posted in the subject line.

For example...

        CHARINDEX('/',[AcctCat])

....will work, but...

        CHARINDEX('/',[AcctCat],5)

....will give me the error message mentioned.

Does anyone know what the deal is with CHARINDEX()?

Thanks for any help anyone can provide,

Conan Kelly



---------------------------
"Smokin' weed kills your brain cells.  Drinkin' only screws up your
liver...ya got 2 a those."
        - Earl Hickey (NBC's "My Name is Earl")

CHARINDEX works fine using the third argument:

CREATE TABLE Foo (
  keycol INT PRIMARY KEY,
  AcctCat VARCHAR(30));

INSERT INTO Foo VALUES(1, 'a/');
INSERT INTO Foo VALUES(2, 'abcdef/');
INSERT INTO Foo VALUES(3, 'agefrtgyu/');
INSERT INTO Foo VALUES(4, NULL);
INSERT INTO Foo VALUES(5, '');

SELECT keycol, CHARINDEX('/', AcctCat, 5) AS position
FROM Foo;

Can you post sample code to reproduce the problem?

Plamen,

Thanks for the feedback.

It looks like CHARINDEX() will work with the 3rd argument...in a
script/query.  Every time I try to create a view in SSMS and use CHARINDEX()
with the 3rd argument.  I get this error:

http://home.att.net/~ctbarbarin/files/function_argument_error.jpg

What I am trying to do is:

I have one column ([AcctCat]) in my table that I need to break down into 3
separate columns in a view.  The data in the [AcctCat] column looks like
this:

A/BCDE/FGHI
A/BCDE/FGH
A/BCD/FGHI
A/BCD/FGH
Z/BCDE/FGHI
Z/BCDE/FGH
Z/BCD/FGHI
Z/BCD/FGH

If you know of another way of doing this without using a three-argument
CHARINDEX(), I'm all ears.

I'm gonna try to create a view with a script.  Maybe I can create a veiw
with a three-argument CHARINDEX() using a script instead of View Designer in
SSMS.

If that doesn't work, I'll create a UDF to split up the column.

Thanks again for all of your help,

Conan






Show quoteHide quote

Seems you are trying to create a view using the designer, which is not a
good option. A better way is to use a script. Try this example, it works
just fine:

CREATE TABLE Foo (
  keycol INT PRIMARY KEY,
  AcctCat VARCHAR(30));

INSERT INTO Foo VALUES(1, 'A/BCDE/FGHI');
INSERT INTO Foo VALUES(2, 'A/BCDE/FGH');
INSERT INTO Foo VALUES(3, 'A/BCD/FGHI');
INSERT INTO Foo VALUES(4, 'A/BCD/FGH');
INSERT INTO Foo VALUES(5, 'Z/BCDE/FGHI');
INSERT INTO Foo VALUES(6, 'Z/BCDE/FGH');
INSERT INTO Foo VALUES(7, 'Z/BCD/FGHI');
INSERT INTO Foo VALUES(8, 'Z/BCD/FGH');

GO

CREATE VIEW FooSplit1 (keycol, AcctCat1, AcctCat2, AcctCat3)
AS
SELECT keycol,
        SUBSTRING(AcctCat, 1, CHARINDEX('/', AcctCat) - 1),
        SUBSTRING(AcctCat, CHARINDEX('/', AcctCat) + 1,
                           CHARINDEX('/', AcctCat, CHARINDEX('/',
AcctCat) + 1) - 3),
        RIGHT(AcctCat, LEN(AcctCat) -
                       CHARINDEX('/', AcctCat, CHARINDEX('/', AcctCat) +
1))
FROM Foo;

GO

SELECT keycol, AcctCat1, AcctCat2, AcctCat3
FROM FooSplit1;

Also, you can use the PARSENAME function:

CREATE VIEW FooSplit2 (keycol, AcctCat1, AcctCat2, AcctCat3)
AS
SELECT keycol,
        PARSENAME(REPLACE(AcctCat, '/', '.'), 3),
        PARSENAME(REPLACE(AcctCat, '/', '.'), 2),
        PARSENAME(REPLACE(AcctCat, '/', '.'), 1)
FROM Foo;

GO

SELECT keycol, AcctCat1, AcctCat2, AcctCat3
FROM FooSplit2;

listing sql server and instances
Collation Issue
Access 2000 or 2003 project - can it connect to sql server 2008?
Downloading Query Analyzer?
performance question - high cpu usage
Active/Active/Active Cluster question
SQL 2000 and 2005 On Save Box
16GB memory for SQL
Oracle Linked Server
Application Event log 19030/19031 Trace stop/start messages.