Char and Varchar: charindex

Showing posts with label charindex. Show all posts

Thursday, February 16, 2012

CHARINDEX with Unicode

To test an idea, I ran the following script in Query Analyser:
DECLARE @.w nvarchar(100)
SET @.w = 'Peter' + NCHAR(65535) + 'Hyssett'
SELECT CHARINDEX(NCHAR(65535), @.w, 1)
This produced a result of 1. Replacing NCHAR(65535) with NCHAR(696) produced
the expected result of 6, while NCHAR(697) produced a result of 1.
Can someone please explain why I did not get a result of 6 for any value of
NCHAR(n) above 255?
Thanks.
--
Peter Hyssett65535 (0xFFFF) is an undefined Unicode code point. SQL essentially ignores
undefined characters in string searches.
For example:
DECLARE @.w nvarchar(100)
SET @.w = 'abc' + NCHAR(65535) + 'def'
SELECT @.w
SELECT CHARINDEX ('cd', @.w, 1)
This returns 3. The undefined character that falls in between "c" and "d"
is ignored, allowing the search for the string "cd" to succeed. Are you
really searching for 0xFFFF, or is there some other character code point
that you are having problems with?
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: CHARINDEX with Unicode
| thread-index: AcTbDqgFDySUytOFRH+uI0ESlvAqnA==| X-WBNR-Posting-Host: 195.92.194.12
| From: "=?Utf-8?B?UGV0ZXIgSHlzc2V0dA==?="
<PeterHyssett@.discussions.microsoft.com>
| Subject: CHARINDEX with Unicode
| Date: Sun, 5 Dec 2004 13:09:06 -0800
| Lines: 14
| Message-ID: <ABA6950A-BD97-46B6-9421-418AED2999FA@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA0
3.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:369843
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| To test an idea, I ran the following script in Query Analyser:
| DECLARE @.w nvarchar(100)
| SET @.w = 'Peter' + NCHAR(65535) + 'Hyssett'
| SELECT CHARINDEX(NCHAR(65535), @.w, 1)
|
| This produced a result of 1. Replacing NCHAR(65535) with NCHAR(696)
produced
| the expected result of 6, while NCHAR(697) produced a result of 1.
|
| Can someone please explain why I did not get a result of 6 for any value
of
| NCHAR(n) above 255?
|
| Thanks.
| --
| Peter Hyssett
||||Thanks, Bart.
No, I just wanted to use a Unicode character as a delimiter to avoid the
hassle of finding delimiter characters in ASCII data. I now know to choose a
Unicode character that actually exists as the delimiter.
"Bart Duncan [MSFT]" wrote:
> 65535 (0xFFFF) is an undefined Unicode code point. SQL essentially ignores
> undefined characters in string searches.
> For example:
> DECLARE @.w nvarchar(100)
> SET @.w = 'abc' + NCHAR(65535) + 'def'
> SELECT @.w
> SELECT CHARINDEX ('cd', @.w, 1)
> This returns 3. The undefined character that falls in between "c" and "d"
> is ignored, allowing the search for the string "cd" to succeed. Are you
> really searching for 0xFFFF, or is there some other character code point
> that you are having problems with?
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> | Thread-Topic: CHARINDEX with Unicode
> | thread-index: AcTbDqgFDySUytOFRH+uI0ESlvAqnA==> | X-WBNR-Posting-Host: 195.92.194.12
> | From: "=?Utf-8?B?UGV0ZXIgSHlzc2V0dA==?="
> <PeterHyssett@.discussions.microsoft.com>
> | Subject: CHARINDEX with Unicode
> | Date: Sun, 5 Dec 2004 13:09:06 -0800
> | Lines: 14
> | Message-ID: <ABA6950A-BD97-46B6-9421-418AED2999FA@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path:
> cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA0
> 3.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:369843
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | To test an idea, I ran the following script in Query Analyser:
> | DECLARE @.w nvarchar(100)
> | SET @.w = 'Peter' + NCHAR(65535) + 'Hyssett'
> | SELECT CHARINDEX(NCHAR(65535), @.w, 1)
> |
> | This produced a result of 1. Replacing NCHAR(65535) with NCHAR(696)
> produced
> | the expected result of 6, while NCHAR(697) produced a result of 1.
> |
> | Can someone please explain why I did not get a result of 6 for any value
> of
> | NCHAR(n) above 255?
> |
> | Thanks.
> | --
> | Peter Hyssett
> |
>

CHARINDEX with Unicode

To test an idea, I ran the following script in Query Analyser:
DECLARE @.w nvarchar(100)
SET @.w = 'Peter' + NCHAR(65535) + 'Hyssett'
SELECT CHARINDEX(NCHAR(65535), @.w, 1)
This produced a result of 1. Replacing NCHAR(65535) with NCHAR(696) produced
the expected result of 6, while NCHAR(697) produced a result of 1.
Can someone please explain why I did not get a result of 6 for any value of
NCHAR(n) above 255?
Thanks.
Peter Hyssett
65535 (0xFFFF) is an undefined Unicode code point. SQL essentially ignores
undefined characters in string searches.
For example:
DECLARE @.w nvarchar(100)
SET @.w = 'abc' + NCHAR(65535) + 'def'
SELECT @.w
SELECT CHARINDEX ('cd', @.w, 1)
This returns 3. The undefined character that falls in between "c" and "d"
is ignored, allowing the search for the string "cd" to succeed. Are you
really searching for 0xFFFF, or is there some other character code point
that you are having problems with?
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: CHARINDEX with Unicode
| thread-index: AcTbDqgFDySUytOFRH+uI0ESlvAqnA==
| X-WBNR-Posting-Host: 195.92.194.12
| From: "=?Utf-8?B?UGV0ZXIgSHlzc2V0dA==?="
<PeterHyssett@.discussions.microsoft.com>
| Subject: CHARINDEX with Unicode
| Date: Sun, 5 Dec 2004 13:09:06 -0800
| Lines: 14
| Message-ID: <ABA6950A-BD97-46B6-9421-418AED2999FA@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!TK2MSFTNGXA0
3.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:369843
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| To test an idea, I ran the following script in Query Analyser:
| DECLARE @.w nvarchar(100)
| SET @.w = 'Peter' + NCHAR(65535) + 'Hyssett'
| SELECT CHARINDEX(NCHAR(65535), @.w, 1)
|
| This produced a result of 1. Replacing NCHAR(65535) with NCHAR(696)
produced
| the expected result of 6, while NCHAR(697) produced a result of 1.
|
| Can someone please explain why I did not get a result of 6 for any value
of
| NCHAR(n) above 255?
|
| Thanks.
| --
| Peter Hyssett
|
|||Thanks, Bart.
No, I just wanted to use a Unicode character as a delimiter to avoid the
hassle of finding delimiter characters in ASCII data. I now know to choose a
Unicode character that actually exists as the delimiter.
"Bart Duncan [MSFT]" wrote:

> 65535 (0xFFFF) is an undefined Unicode code point. SQL essentially ignores
> undefined characters in string searches.
> For example:
> DECLARE @.w nvarchar(100)
> SET @.w = 'abc' + NCHAR(65535) + 'def'
> SELECT @.w
> SELECT CHARINDEX ('cd', @.w, 1)
> This returns 3. The undefined character that falls in between "c" and "d"
> is ignored, allowing the search for the string "cd" to succeed. Are you
> really searching for 0xFFFF, or is there some other character code point
> that you are having problems with?
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> | Thread-Topic: CHARINDEX with Unicode
> | thread-index: AcTbDqgFDySUytOFRH+uI0ESlvAqnA==
> | X-WBNR-Posting-Host: 195.92.194.12
> | From: "=?Utf-8?B?UGV0ZXIgSHlzc2V0dA==?="
> <PeterHyssett@.discussions.microsoft.com>
> | Subject: CHARINDEX with Unicode
> | Date: Sun, 5 Dec 2004 13:09:06 -0800
> | Lines: 14
> | Message-ID: <ABA6950A-BD97-46B6-9421-418AED2999FA@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path:
> cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!TK2MSFTNGXA0
> 3.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:369843
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | To test an idea, I ran the following script in Query Analyser:
> | DECLARE @.w nvarchar(100)
> | SET @.w = 'Peter' + NCHAR(65535) + 'Hyssett'
> | SELECT CHARINDEX(NCHAR(65535), @.w, 1)
> |
> | This produced a result of 1. Replacing NCHAR(65535) with NCHAR(696)
> produced
> | the expected result of 6, while NCHAR(697) produced a result of 1.
> |
> | Can someone please explain why I did not get a result of 6 for any value
> of
> | NCHAR(n) above 255?
> |
> | Thanks.
> | --
> | Peter Hyssett
> |
>

Charindex to not include the delimeter question

Hi, I have this:
select left (mycategory, CHARINDEX( ':', mycategory) - 1) as Cat
from allCats
If I have a column that has no ':' colon in it I get an error about
invalid string. As long as there actually is a delimeter then all is
well. But there isn't always a delimeter in the column value. How can
I make it not give me the error when the column does not have the
delimeter?
Thank you for any help.One option might be to do the following:
SELECT Cat = CASE WHEN myCategory LIKE '%:%' THEN left (mycategory,
CHARINDEX( ':', mycategory) - 1)
ELSE myCategory END
FROM allCats
HTH
Stu|||> How can
> I make it not give me the error when the column does not have the
> delimeter?
Try:
SELECT
CASE CHARINDEX( ':', mycategory)
WHEN 0 THEN mycategory
ELSE LEFT(mycategory, CHARINDEX( ':', mycategory) - 1) END AS Cat
FROM allCats
Hope this helps.
Dan Guzman
SQL Server MVP
<needin4mation@.gmail.com> wrote in message
news:1146795826.199177.38170@.v46g2000cwv.googlegroups.com...
> Hi, I have this:
> select left (mycategory, CHARINDEX( ':', mycategory) - 1) as Cat
> from allCats
> If I have a column that has no ':' colon in it I get an error about
> invalid string. As long as there actually is a delimeter then all is
> well. But there isn't always a delimeter in the column value. How can
> I make it not give me the error when the column does not have the
> delimeter?
> Thank you for any help.
>|||Another alternative is
select
left(mycategory, charindex(':', mycategory+':') - 1) as Cat
from allCats
Steve Kass
Drew University
needin4mation@.gmail.com wrote:

>Hi, I have this:
>select left (mycategory, CHARINDEX( ':', mycategory) - 1) as Cat
>from allCats
>If I have a column that has no ':' colon in it I get an error about
>invalid string. As long as there actually is a delimeter then all is
>well. But there isn't always a delimeter in the column value. How can
>I make it not give me the error when the column does not have the
>delimeter?
>Thank you for any help.
>
>|||That's very nice; subtle, though.
Stu|||Anyone have any ideas on how to traverse the entire string instead of
just first occurrence?
Thanks for all the answers.
Stu wrote:
> That's very nice; subtle, though.
> Stu|||Using a numbers table as in http://www.aspfaq.com/show.asp?id=2516
you can do this
select substring(mycategory,
Number,
charindex(':',
mycategory + ':',
Number) - Number) as Cat
from allCats
inner join Numbers on Number between 1 and len(mycategory) + 1
and substring(':' + mycategory, Number, 1) = ':'|||Aside from the table-of-numbers method, you'll need to resort to procedural
looping. You can encapsulate the code in a user-defined function to
facilitate reuse. In SQL 2005, you also have CLR languages available which
can do string parsing and manipulation more efficiently than Transact-SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
<needin4mation@.gmail.com> wrote in message
news:1146868473.352163.33280@.j73g2000cwa.googlegroups.com...
> Anyone have any ideas on how to traverse the entire string instead of
> just first occurrence?
> Thanks for all the answers.
> Stu wrote:
>

CHARINDEX starting from the end?

Hi,
I have a problem finding the last occurrence of a character in a string.
The charindex seems just able to search from left to right..
Anyone pleas give me a hand.
/Memgardsee if this helps...

Code:
------------------------------
declare @.s varchar(17)
set @.s = '1234|67890A|CDEFG'
select @.s as 'Original String'
select reverse(@.s) as 'In Reverse', datalength(@.s) as 'String Length'
select charindex('|',reverse(@.s)) as 'Location of first ''|'' in Reverse String'
select datalength(@.s) - charindex('|',reverse(@.s)) + 1 as 'Location of last ''|'' in Original string'
------------------------------|||That did help.
Thanks a lot!

Stange that SQL Server dosn't support this with a built in function.
In Oracle you can just add a negative value to scan from the right to left...|||I have found over the years that Oracle and SQL Server both get the job done, just in diffrent ways.

CHARINDEX returns zero in TEXT column

I'm running into an issue where CHARINDEX on a text datatype column returns
0
if the expression I'm searching for exists at a position greater than 8000.
For example:
use pubs;
select charindex('New Moon Books',pr_info,8000)
from pub_info
returns zero, even though I know 'New Moon Books' exists past character
8000. Is this a known issue, and is there a workaround? It's causing my
search and replace procedure (using the UPDATETEXT function) to fail, i.e.
LIKE '%searchfor%'
is true but
CHARINDEX('searchfor',textColumn)
is zero.
Any help is appreciated.Did you try PATINDEX?
"Alan Smithee" <AlanSmithee@.discussions.microsoft.com> wrote in message
news:F8D5F76C-9114-47F1-A584-96035BFA38B6@.microsoft.com...
> I'm running into an issue where CHARINDEX on a text datatype column
> returns 0
> if the expression I'm searching for exists at a position greater than
> 8000.
> For example:
> use pubs;
> select charindex('New Moon Books',pr_info,8000)
> from pub_info
> returns zero, even though I know 'New Moon Books' exists past character
> 8000. Is this a known issue, and is there a workaround? It's causing my
> search and replace procedure (using the UPDATETEXT function) to fail, i.e.
> LIKE '%searchfor%'
> is true but
> CHARINDEX('searchfor',textColumn)
> is zero.
> Any help is appreciated.|||CHARINDEX will not work for strings larger than 8000. To work with TEXT
fields larger than this size, you will need to use the TEXT functions in SQL
Server 2000 like READTEXT, WRITETEXT etc.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Alan Smithee" <AlanSmithee@.discussions.microsoft.com> wrote in message
news:F8D5F76C-9114-47F1-A584-96035BFA38B6@.microsoft.com...
> I'm running into an issue where CHARINDEX on a text datatype column
> returns 0
> if the expression I'm searching for exists at a position greater than
> 8000.
> For example:
> use pubs;
> select charindex('New Moon Books',pr_info,8000)
> from pub_info
> returns zero, even though I know 'New Moon Books' exists past character
> 8000. Is this a known issue, and is there a workaround? It's causing my
> search and replace procedure (using the UPDATETEXT function) to fail, i.e.
> LIKE '%searchfor%'
> is true but
> CHARINDEX('searchfor',textColumn)
> is zero.
> Any help is appreciated.|||charindex won't work with text datatype. Use patindex|||Thanks Aaron, you of course are correct, PATINDEX works! (I was sure I had
tried that before, but I think I left out the wildcard character).
Anyway, much thanks (and thanks to Omnibuzz too!)
A.S.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Did you try PATINDEX?
>
> "Alan Smithee" <AlanSmithee@.discussions.microsoft.com> wrote in message
> news:F8D5F76C-9114-47F1-A584-96035BFA38B6@.microsoft.com...
>
>

charindex question

I have a field containing a string with '/' in it multiple times.

How can I return the charindex of the last occurance of '/' in the
string?

Regards,
Ciarndeclare @.s varchar(10)
set @.s='as/gf/af/h'
select len(@.s)-charindex('/',reverse(@.s))+1

Madhivanan

CHARINDEX is not working

i am using the below code but its not working .. what could be the reason?
CHARINDEX(@.SPName,@.DateAndIntCols)
to CHARINDEX function i am passing to variables, cant i pass variables to
CHARINDEX?Please elaborate on "not working" . Are there errors? Don't you get expected
results? Don't you get any results? What?
ML
http://milambda.blogspot.com/|||On Thu, 1 Dec 2005 15:33:02 -0800, KL wrote:

>i am using the below code but its not working .. what could be the reason?
>CHARINDEX(@.SPName,@.DateAndIntCols)
>to CHARINDEX function i am passing to variables, cant i pass variables to
>CHARINDEX?
Hi KL,
The following code returns 4 on my computer, which is as expected. What
are the values of @.SPName and @.DateAndIntCols in your case, and what was
the result you got?
DECLARE @.SPName varchar(10), @.DateAndIntCols varchar(100)
SET @.SPName = 'xyz'
SET @.DateAndIntCols = 'uvwxyzabcdef'
SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I am passing the below values to the variables but its still returning 0...
SET @.SPName ='eUpdated'
SET @.DateAndIntCols = 'eUpdated,eDeleted'
but its working (returning 1) when use the following one...
SET @.DateAndIntCols = 'eUpdated,eDeleted'
CHARINDEX('eUpdated',@.DateAndIntCols)
"ML" wrote:

> Please elaborate on "not working" . Are there errors? Don't you get expect
ed
> results? Don't you get any results? What?
>
> ML
> --
> http://milambda.blogspot.com/|||On Thu, 1 Dec 2005 16:03:03 -0800, KL wrote:

>I am passing the below values to the variables but its still returning 0...
>SET @.SPName ='eUpdated'
>SET @.DateAndIntCols = 'eUpdated,eDeleted'
>but its working (returning 1) when use the following one...
>SET @.DateAndIntCols = 'eUpdated,eDeleted'
>CHARINDEX('eUpdated',@.DateAndIntCols)
Hi KL,
Works for me:
DECLARE @.SPName varchar(10), @.DateAndIntCols varchar(100)
SET @.SPName ='eUpdated'
SET @.DateAndIntCols = 'eUpdated,eDeleted'
SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
Can you please post your complete code?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Here is the code i am using ....
DECLARE
@.SPName varchar(128) ,
@.DateAndIntCols varchar(1000)
SELECT @.SPName='eUpdated'
SELECT @.DateAndIntCols=DateAndIntCols FROM MetaData WHERE SPID='CC-3'
SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
And when i printed the variable @.DateAndIntCols its showing as
'eUpdated,eDeleted'....
"Hugo Kornelis" wrote:

> On Thu, 1 Dec 2005 16:03:03 -0800, KL wrote:
>
> Hi KL,
> Works for me:
> DECLARE @.SPName varchar(10), @.DateAndIntCols varchar(100)
> SET @.SPName ='eUpdated'
> SET @.DateAndIntCols = 'eUpdated,eDeleted'
> SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
>
> Can you please post your complete code?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Here is the complete code i am using ....
DECLARE
@.SPName varchar(128) ,
@.DateAndIntCols varchar(1000)
SELECT @.SPName='eUpdated'
SELECT @.DateAndIntCols=DateAndIntCols FROM MetaData WHERE SPID='CC-3'
SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
And when i printed the variable @.DateAndIntCols its showing as
'eUpdated,eDeleted'....
"Hugo Kornelis" wrote:

> On Thu, 1 Dec 2005 16:03:03 -0800, KL wrote:
>
> Hi KL,
> Works for me:
> DECLARE @.SPName varchar(10), @.DateAndIntCols varchar(100)
> SET @.SPName ='eUpdated'
> SET @.DateAndIntCols = 'eUpdated,eDeleted'
> SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
>
> Can you please post your complete code?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Thu, 1 Dec 2005 16:26:02 -0800, KL wrote:

>Here is the complete code i am using ....
>DECLARE
>@.SPName varchar(128) ,
>@.DateAndIntCols varchar(1000)
>SELECT @.SPName='eUpdated'
>SELECT @.DateAndIntCols=DateAndIntCols FROM MetaData WHERE SPID='CC-3'
>SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
>And when i printed the variable @.DateAndIntCols its showing as
>'eUpdated,eDeleted'....
Hi KL,
Since I don;t have your table, I had to add some lines to the script.
Here's what I executed:
CREATE TABLE MetaData
(SPID char(4) NOT NULL PRIMARY KEY,
DateAndIntCols varchar(1000) NOT NULL)
INSERT INTO MetaData (SPID, DateAndIntCols)
VALUES ('CC-3', 'eUpdated,eDeleted')
go
DECLARE
@.SPName varchar(128) ,
@.DateAndIntCols varchar(1000)
SELECT @.SPName='eUpdated'
SELECT @.DateAndIntCols=DateAndIntCols FROM MetaData WHERE SPID='CC-3'
SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
go
DROP TABLE MetaData
go
And here's the output:
1
This is the expected output. What output are you getting? Also, what is
the output if you execute
SELECT @.@.VERSION
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

CHARINDEX in user-defined function

>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Sat, 2 Oct 2004 17:45:06 -0400, Aaron [SQL Server MVP] wrote:

>The view workaround is suspect also. See http://www.aspfaq.com/2439 for
>other workarounds and a link to a discussion about the inconsistency of the
>results from a view...
Hi Aaron,
Thanks for the heads-up!
Good article - and a very interesting script in Tibor's proof. I ran it
several times and got 5, 6, 7, 8 or 9 results - and often, they were not
even distinct at all!!
I wish I had encountered this link before I tried to explain to the OP why
UDF's can't contain non-deterministic functions...
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Sun, 3 Oct 2004 11:12:17 +0200, Agoston Bejo wrote:

>Hi!
>What you told solved the problem completely. I have some minor questions
>left, however:
Hi Agoston,
I hope you also read the article posted by Aaron (in reply to my message)
which shows why the workaround with a view has some problems as well.
Please follow his advice instead of my original advice.
I'll type the answers to your minor questions inline.

>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> az albbiakat rta a kvetkezo
>hrzenetben: dc2ul09hsjru7d0der6jpqoe2cbics6e2m@.4ax.com...
>I see, only to be clear on the case: does 'non-deterministic' in this
>terminology also mean that the function has side effects? It is possible
>that a 'non-deterministic' function (in the classical sense) may not change
>anything in the database.
No, 'non-deterministic' in this terminology means *only* whether the
function is deterministic or not - in other words: if the result of the
function (with the same arguments) will always be the same or not.
Not having side effects is *another* requirement for user-defined
functions (UDFs). I've seen some workarounds to this requirement posted in
these newsgroups as well, but I'd recommend against using them - there is
some solid reasoning behind the requirement of not having side effects.
The main reason is the fact that the optimizer is free to choose different
query plans, that might involve more or less calls to the UDFs used in the
query, and (if columns are used as parameters) different sequences. The
result of the query becomes inpredictable.

>From the MSDN:
>"All built-in string functions, except for CHARINDEX and PATINDEX, are
>deterministic."
>(URL:
>http://msdn.microsoft.com/library/de...us/tsqlref/ts_
>fa-fz_7oqb.asp - Transact-SQL Reference / Built-in functions)
>But I didn't get any error message, and CHARINDEX worked indeed in my
>function.
>Is the MSDN lying?

It isn't lying. Unclear, yes. But not lying.
First, let me give you a quote that is more relevant for you. The URL is
http://msdn.microsoft.com/library/en...s_08_460j.asp.
(start quote)
Built-in functions that can return different data on each call are not
allowed in user-defined functions. The built-in functions not allowed in
user-defined functions are:
@.@.CONNECTIONS @.PACK_SENT GETDATE
@.@.CPU_BUSY @.PACKET_ERRORS GetUTCDate
@.@.IDLE @.TIMETICKS NEWID
@.@.IO_BUSY @.TOTAL_ERRORS RAND
@.@.MAX_CONNECTIONS @.@.TOTAL_READ TEXTPTR
@.@.PACK_RECEIVED @.@.TOTAL_WRITE
(end quote)
The quote you gave relates to determining whether your UDF will be
considered deterministic or non-deterministic. Since it contains a call to
CHARINDEX (which is considerde non-determinstic), your UDS will be
considered non-deterministic as well. This means that you can't create an
index on a view or computed column that is creating using this UDF. As
long as that is not your intention, it should not bother you.
(By the way - I do admit I often wondered WHY SQL Server regards CHARINDEX
and PATINDEX to be nondeterministic).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||For fun, give this a try (using the framework of Tibor's example):
SELECT foo, count(*) as ct
FROM (
SELECT dbo.foo1() as foo
FROM Northwind.dbo."Order Details" AS od
INNER JOIN Northwind.dbo.Orders AS o
ON o.OrderId = od.OrderID
) T
GROUP BY foo
You are likely to get a result set containing several rows, all of which
have the same value of foo. Even adding DISTINCT to the outer query
does not guarantee a result set with distinct rows.
SK
Hugo Kornelis wrote:

>On Sat, 2 Oct 2004 17:45:06 -0400, Aaron [SQL Server MVP] wrote:
>
>
>Hi Aaron,
>Thanks for the heads-up!
>Good article - and a very interesting script in Tibor's proof. I ran it
>several times and got 5, 6, 7, 8 or 9 results - and often, they were not
>even distinct at all!!
>I wish I had encountered this link before I tried to explain to the OP why
>UDF's can't contain non-deterministic functions...
>Best, Hugo
>
|||Hi,
I wrote the following replacement for CHARINDEX, which is
deterministic, albeit slower than the builtin CHARINDEX function. But
since it is deterministic, I can use it in indexed views, etc...
create function dbo.fn_CHARINDEX(@.ch char(1), @.s varchar(8000))
returns int with schemabinding as
begin
declare @.i int
set @.i = 1
while @.i <= len(@.s) begin
if substring(@.s,@.i,1) = @.ch
return @.i
set @.i = @.i + 1
end
return NULL
end
Cheers
Simon Kissane
http://simonkissane.blogspot.com/

charindex in user-defined function

CHARINDEX in user-defined function

charindex in user-defined function

CHARINDEX in user-defined function

Hi,
I'm trying to use the CHARINDEX built-in function in my user-defined
function, but it doesn't seem to work.
Right now I got down to only write a test-function that doesn't do anything
else but call charindex:
CREATE FUNCTION X(@.p_str1 VARCHAR, @.p_str2 VARCHAR)
RETURNS INT AS
BEGIN
RETURN CHARINDEX(@.p_str1, @.p_str2);
END
Now, when I do a simple select like this:
SELECT NAME, dbo.X('Smith', NAME) AS Expr1, CHARINDEX('Smith', NAME) AS
Expr2 FROM PERSONS
The second column will be 0 everywhere, so rows like this occur:
John Smith, 0, 6
I've read that non-deterministic functions are not allowed to be used in
user-defined functions, and I suspect that this is what causes this.
In this case:
1. Why can't non-deterministic functions be used?
2. Shouldn't there be some error thrown in dbo.X() instead of it simply
returning 0?
3. Is there some similar deterministic function that can be used? I really
need this function for doing some basic parsing, I'm don't want write my own
in vain.
4. How on Earth can such a simple function be non-deterministic?
ThxOn Sat, 2 Oct 2004 21:39:44 +0200, Agoston Bejo wrote:
>CREATE FUNCTION X(@.p_str1 VARCHAR, @.p_str2 VARCHAR)
The default length for a varchar is 1 byte. You might want to change the
above line to something like:
CREATE FUNCTION X(@.p_str1 VARCHAR(20), @.p_str2 VARCHAR(50))
(20 and 50 used as example - se proper judgement to find the best length
for your function). This will solve your problem.
Now on to your questions:
>1. Why can't non-deterministic functions be used?
Because (unlike views) a user-defined function gets called multiple times
during the execution of one query; if the results change from call to
call, all kinds of nasty side-effects will kick in,
>2. Shouldn't there be some error thrown in dbo.X() instead of it simply
>returning 0?
If you attempt to create a user-defined function with a non-deterministic
function, you'll get an error message.
>3. Is there some similar deterministic function that can be used? I really
>need this function for doing some basic parsing, I'm don't want write my own
>in vain.
A workaround if you need to use a non-deterministic function in your
userdefined function is to put the nondeterministic function in a view and
reference the view from within the function. This can not be used for all
non-deterministic functions, but it's a common workaround for including
the current date and time (getdate()) in a UDF.
>4. How on Earth can such a simple function be non-deterministic?
It isn't. :-)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> A workaround if you need to use a non-deterministic function in your
> userdefined function is to put the nondeterministic function in a view and
> reference the view from within the function. This can not be used for all
> non-deterministic functions, but it's a common workaround for including
> the current date and time (getdate()) in a UDF.
The view workaround is suspect also. See http://www.aspfaq.com/2439 for
other workarounds and a link to a discussion about the inconsistency of the
results from a view...
A|||Hi!
What you told solved the problem completely. I have some minor questions
left, however:
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> az alábbiakat írta a következo
hírüzenetben: dc2ul09hsjru7d0der6jpqoe2cbics6e2m@.4ax.com...
> >1. Why can't non-deterministic functions be used?
> Because (unlike views) a user-defined function gets called multiple times
> during the execution of one query; if the results change from call to
> call, all kinds of nasty side-effects will kick in,
I see, only to be clear on the case: does 'non-deterministic' in this
terminology also mean that the function has side effects? It is possible
that a 'non-deterministic' function (in the classical sense) may not change
anything in the database.
> >4. How on Earth can such a simple function be non-deterministic?
> It isn't. :-)
From the MSDN:
"All built-in string functions, except for CHARINDEX and PATINDEX, are
deterministic."
(URL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
fa-fz_7oqb.asp - Transact-SQL Reference / Built-in functions)
But I didn't get any error message, and CHARINDEX worked indeed in my
function.
Is the MSDN lying? :)
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sat, 2 Oct 2004 17:45:06 -0400, Aaron [SQL Server MVP] wrote:
>The view workaround is suspect also. See http://www.aspfaq.com/2439 for
>other workarounds and a link to a discussion about the inconsistency of the
>results from a view...
Hi Aaron,
Thanks for the heads-up!
Good article - and a very interesting script in Tibor's proof. I ran it
several times and got 5, 6, 7, 8 or 9 results - and often, they were not
even distinct at all!!
I wish I had encountered this link before I tried to explain to the OP why
UDF's can't contain non-deterministic functions...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sun, 3 Oct 2004 11:12:17 +0200, Agoston Bejo wrote:
>Hi!
>What you told solved the problem completely. I have some minor questions
>left, however:
Hi Agoston,
I hope you also read the article posted by Aaron (in reply to my message)
which shows why the workaround with a view has some problems as well.
Please follow his advice instead of my original advice.
I'll type the answers to your minor questions inline.
>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> az alábbiakat írta a következo
>hírüzenetben: dc2ul09hsjru7d0der6jpqoe2cbics6e2m@.4ax.com...
>> >1. Why can't non-deterministic functions be used?
>> Because (unlike views) a user-defined function gets called multiple times
>> during the execution of one query; if the results change from call to
>> call, all kinds of nasty side-effects will kick in,
>I see, only to be clear on the case: does 'non-deterministic' in this
>terminology also mean that the function has side effects? It is possible
>that a 'non-deterministic' function (in the classical sense) may not change
>anything in the database.
No, 'non-deterministic' in this terminology means *only* whether the
function is deterministic or not - in other words: if the result of the
function (with the same arguments) will always be the same or not.
Not having side effects is *another* requirement for user-defined
functions (UDFs). I've seen some workarounds to this requirement posted in
these newsgroups as well, but I'd recommend against using them - there is
some solid reasoning behind the requirement of not having side effects.
The main reason is the fact that the optimizer is free to choose different
query plans, that might involve more or less calls to the UDFs used in the
query, and (if columns are used as parameters) different sequences. The
result of the query becomes inpredictable.
>> >4. How on Earth can such a simple function be non-deterministic?
>> It isn't. :-)
>From the MSDN:
>"All built-in string functions, except for CHARINDEX and PATINDEX, are
>deterministic."
>(URL:
>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
>fa-fz_7oqb.asp - Transact-SQL Reference / Built-in functions)
>But I didn't get any error message, and CHARINDEX worked indeed in my
>function.
>Is the MSDN lying? :)
It isn't lying. Unclear, yes. But not lying.
First, let me give you a quote that is more relevant for you. The URL is
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_08_460j.asp.
(start quote)
Built-in functions that can return different data on each call are not
allowed in user-defined functions. The built-in functions not allowed in
user-defined functions are:
@.@.CONNECTIONS @.PACK_SENT GETDATE
@.@.CPU_BUSY @.PACKET_ERRORS GetUTCDate
@.@.IDLE @.TIMETICKS NEWID
@.@.IO_BUSY @.TOTAL_ERRORS RAND
@.@.MAX_CONNECTIONS @.@.TOTAL_READ TEXTPTR
@.@.PACK_RECEIVED @.@.TOTAL_WRITE
(end quote)
The quote you gave relates to determining whether your UDF will be
considered deterministic or non-deterministic. Since it contains a call to
CHARINDEX (which is considerde non-determinstic), your UDS will be
considered non-deterministic as well. This means that you can't create an
index on a view or computed column that is creating using this UDF. As
long as that is not your intention, it should not bother you.
(By the way - I do admit I often wondered WHY SQL Server regards CHARINDEX
and PATINDEX to be nondeterministic).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||For fun, give this a try (using the framework of Tibor's example):
SELECT foo, count(*) as ct
FROM (
SELECT dbo.foo1() as foo
FROM Northwind.dbo."Order Details" AS od
INNER JOIN Northwind.dbo.Orders AS o
ON o.OrderId = od.OrderID
) T
GROUP BY foo
You are likely to get a result set containing several rows, all of which
have the same value of foo. Even adding DISTINCT to the outer query
does not guarantee a result set with distinct rows.
SK
Hugo Kornelis wrote:
>On Sat, 2 Oct 2004 17:45:06 -0400, Aaron [SQL Server MVP] wrote:
>
>>The view workaround is suspect also. See http://www.aspfaq.com/2439 for
>>other workarounds and a link to a discussion about the inconsistency of the
>>results from a view...
>>
>Hi Aaron,
>Thanks for the heads-up!
>Good article - and a very interesting script in Tibor's proof. I ran it
>several times and got 5, 6, 7, 8 or 9 results - and often, they were not
>even distinct at all!!
>I wish I had encountered this link before I tried to explain to the OP why
>UDF's can't contain non-deterministic functions...
>Best, Hugo
>|||Hi,
I wrote the following replacement for CHARINDEX, which is
deterministic, albeit slower than the builtin CHARINDEX function. But
since it is deterministic, I can use it in indexed views, etc...
create function dbo.fn_CHARINDEX(@.ch char(1), @.s varchar(8000))
returns int with schemabinding as
begin
declare @.i int
set @.i = 1
while @.i <= len(@.s) begin
if substring(@.s,@.i,1) = @.ch
return @.i
set @.i = @.i + 1
end
return NULL
end
Cheers
Simon Kissane
http://simonkissane.blogspot.com/

CHARINDEX in CASE Within SELECT Statement

Hello.
I need help with using CHARINDEX.
I have a column in a table (Discount_Specification) that could hold the
following values:
LF(I03U,CHA-14,ALL-0)
MR(I05U,I06U,CHA-5)
etc.
I'm inserting into another table and need to pick up the value following
"CHA-" in that column.
I've created a User Defined Function to do this but would like to make the
SQL code more efficient. The UDF has an argument which specifies which value
to pick up (separated by the delimiter). In example 1 it's the 2nd value. In
example 2 it's the 3rd value.
My SELECT code looks something like this:
select provider_id, last_name, first_name,
CASE
WHEN CHARINDEX(@.CHA,SM.Discount_Specification) > 0 THEN
CASE
WHEN Left(SM.Discount_Specification,2) IN(@.LF, @.LS, @.PF, @.PL) THEN
webcentral.dbo.udf_ConvertDecimalAllowance
(WebCentral.dbo.udf_GetNthDecimalValue(WebCentral.dbo.udf_GetNthTextValue(We
bCentral.dbo.udf_GetNthTextValue
(SubString(SM. Discount_Specification,4,DataLength(RTri
m(SM.Discount_Specific
ation))-4),@.Comma,2),@.Dash,2),@.Comma,1))
ELSE
CASE
WHEN Left(SM.Discount_Specification,3) = @.CHA THEN
-- Charge is all by itself
webcentral.dbo.udf_ConvertDecimalAllowance
(WebCentral.dbo.udf_GetNthDecimalValue(WebCentral.dbo.udf_GetNthTextValue
(RTrim(SM. Discount_Specification),@.Dash,2),@.Comma,
1))
ELSE 1
END
END
I would like to use a variable instead.
Something like:
DECLARE @.Pos SmallInt
SELECT provider_id, last_name, first_name,
@.Pos = CHARINDEX(@.CHA,SM.Discount_Specification)
CASE
WHEN @.POS > 0 THEN
webcentral.dbo.udf_ConvertDecimalAllowance etc. etc.
END
I get an error on the line where I'm setting @.Pos and I don't know what
syntax to use.
Any suggestions will be greatly appreciated.
Thanks,
RitaYou cannot set variables and return results to the client in the same SELECT
statement.
I'm not sure what it really is that you're trying to achieve, but I think
this function might help you parse those strings:
create function dbo.fnParse
(
@.charValue varchar(1000)
,@.findThis varchar(1000) = null
)
returns int
as
begin
declare @.result int
set @.findThis = isnull(@.findThis, 'CHA-')
select @.charValue
= substring(@.charValue, charindex(@.findThis, @.charValue) +
len(@.findThis), len(@.charValue))
select @.charValue
= substring(@.charValue, 1, patindex('%[^0-9]%', @.charValue) - 1)
select @.result
= case
when isnumeric(@.charValue) = 1
then cast(@.charValue as int)
else null
end
return @.result
end
go
Use like this:
select dbo.fnParse('LF(I03U,CHA-14,ALL-0)', 'CHA-')
,dbo.fnParse('MR(I05U,I06U,CHA-5)', 'CHA-')
ML
http://milambda.blogspot.com/|||Thanks so much for your response.
That's exactly what I'm playing around with - creating a UDF that uses the
CHARINDEX. Your example helps a lot!
Rita
"ML" wrote:

> You cannot set variables and return results to the client in the same SELE
CT
> statement.
> I'm not sure what it really is that you're trying to achieve, but I think
> this function might help you parse those strings:
> create function dbo.fnParse
> (
> @.charValue varchar(1000)
> ,@.findThis varchar(1000) = null
> )
> returns int
> as
> begin
> declare @.result int
> set @.findThis = isnull(@.findThis, 'CHA-')
> select @.charValue
> = substring(@.charValue, charindex(@.findThis, @.charValue) +
> len(@.findThis), len(@.charValue))
> select @.charValue
> = substring(@.charValue, 1, patindex('%[^0-9]%', @.charValue) - 1)
> select @.result
> = case
> when isnumeric(@.charValue) = 1
> then cast(@.charValue as int)
> else null
> end
> return @.result
> end
> go
> Use like this:
> select dbo.fnParse('LF(I03U,CHA-14,ALL-0)', 'CHA-')
> ,dbo.fnParse('MR(I05U,I06U,CHA-5)', 'CHA-')
>
> ML
> --
> http://milambda.blogspot.com/|||Gee, that sounds like a good deed from me. Hope Santa reads this newsgroup.
:)
At least one of the elves should. Or is Santa not using SQL...?
Anyway, just remember that this *is* the newsgroup with solutions. :)
ML
http://milambda.blogspot.com/|||>> Any suggestions will be greatly appreciated. <<
1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
2) Learn to normalize your schema and stop writing COBOL-style string
manipulations in procedures. You will waste an insane amount of time
and spce doing this.
I also watched code like this kill some children in Africa. The
programmer had used strings hold the package size quantity for drugs.
When the drug suppliers agreed to provide smaller packages (i.e.
quantity one), the string was changed, but not the front end. The
result was when you thought you had ordered a 5-unit package, you got
a 1-unit package instead.
My guess is that you need a table of Discounts (notice the plural name
to show it is a set) with the amount, the source and the code for each
of the discounts. You then do a simple join and get rid of all that
"pseudo-COBOL" field extractions.|||Ouch!
I think I should have posted everything here pertaining to my question so
there would be no misunderstanding.
I have no control of the data coming in. We are supplied this by our clients
and then we have to import the non standard data into our standard SQL table
s.
No matter what, I can't get passed having to parse out bits of information
all strung
together within 1 column separated by a delimiter.
"--CELKO--" wrote:

> 1) Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are.
> 2) Learn to normalize your schema and stop writing COBOL-style string
> manipulations in procedures. You will waste an insane amount of time
> and spce doing this.
> I also watched code like this kill some children in Africa. The
> programmer had used strings hold the package size quantity for drugs.
> When the drug suppliers agreed to provide smaller packages (i.e.
> quantity one), the string was changed, but not the front end. The
> result was when you thought you had ordered a 5-unit package, you got
> a 1-unit package instead.
> My guess is that you need a table of Discounts (notice the plural name
> to show it is a set) with the amount, the source and the code for each
> of the discounts. You then do a simple join and get rid of all that
> "pseudo-COBOL" field extractions.
>|||>> have no control of the data coming in. We are supplied this by our clien
ts and then we have to import the non standard data into our standard SQL ta
bles. No matter what, I can't get passed having to parse out bits of informa
tion all strung together wi
thin 1 column separated by a delimiter. <<
Just because the source data is a mess, you are not required to
propagate it in the schema. Parse it at load time and edit everything.
Have you looked into an ETL tool of some kind? You might be able to
write something in a small, fast scripting language likie AWK, Perl,
etc.|||Hmm.
I never thought to do that. The input files are always imported "as is" into
SQL staging tables using DTS packages. The reason being if there is ever a
question regarding the output data we have the original data in SQL format
against which queries can be run.
I use an ActiveX script to popultae the columns so that may be where I could
parse out the values. I've used AWK and Perl sparingly in the past. There is
a lot of logic going on when I get the value out of the string as to where t
o
place it so I think ActiveX is the best way to go.
I also need to consider speed since some of the files have millions of rows
in them.
Would it be faster to parse within the DTS ActiveX script or the Stored
Procedure (which I'm currently doing)? I heard that Stored Procedures are
much faster than DTS packages.
Thanks for the suggestion.
"--CELKO--" wrote:

within 1 column separated by a delimiter. <<
> Just because the source data is a mess, you are not required to
> propagate it in the schema. Parse it at load time and edit everything.
> Have you looked into an ETL tool of some kind? You might be able to
> write something in a small, fast scripting language likie AWK, Perl,
> etc.
>|||A set-based solution will be more efficient if run on the server. But that
will require accessing the source files through a linked server. So, the
question of the day is - what type are the source files?
ML
http://milambda.blogspot.com/|||They're fixed length text files.
"ML" wrote:

> A set-based solution will be more efficient if run on the server. But that
> will require accessing the source files through a linked server. So, the
> question of the day is - what type are the source files?
>
> ML
> --
> http://milambda.blogspot.com/

charindex faster than like operator?

Hi ppl,
While I was working on the post "compare 2 strings" in my test db,
I came across this.
I ran these queries, the ones using the charindex and patindex were faster
than the like operator. I ran it in different tables with different inputs.
But same result.
I know server configs can affect the processing. But can some one run it in
their table and let me know. Plz. I am wordering whats the difference. Any
light on this?
Eager to know. Thanks a lot.
select top 100 * from tbl1 where namecol like '%food%'
select top 100 * from tbl1 where charindex('food',namecol) > 0
select top 100 * from tbl1 where patindex('%food%',namecol) > 0
select top 100 * from tbl1 where 'ab,cd' like '%' + namecol + '%'
select top 100 * from tbl1 where charindex(namecol,'ab,cd') > 0
select top 100 * from tbl1 where patindex('%' + namecol + '%','ab,cd') > 0use this.. and let me know if it works
select a.CoName from oldTbl a
where exists ( select 1 from curtbl b where b.CoName like '%' + a.CoName +
'%')

CHARINDEX doesn't work

My charindex seems to always return 0 no matter what. I tried it on
different SQL servers and I always get 0 no matter what. Here was a
test script I tried and still got zero.
DECLARE @.myvar as varchar(25)
DECLARE @.myvar2 as varchar(25)
SET @.myvar = 'hello'
SET @.myvar2 = 'll'
PRINT CHARINDEX( @.myvar, @.myvar2 )
PRINT CHARINDEX( 'test', 's' )
Both print zero... can anyone tell me what i'm doing wrong?Never mind.. I had the parameters mixed up. What a dumb mistake.|||try this, you reversed them!
DECLARE @.myvar as varchar(25)
DECLARE @.myvar2 as varchar(25)
SET @.myvar = 'hello'
SET @.myvar2 = 'll'
PRINT CHARINDEX( @.myvar2, @.myvar )
PRINT CHARINDEX( 's','test' )
http://sqlservercode.blogspot.com/

CHARINDEX and STUFF/REPLACE

Hello,

I need to be able to replace only the first occurance of a space character
in a column.
Reason being is the data in the column I am trying to replace seems to have
umpteen space characters after each entry, so a simple replace function
will replace all the spaces after it with what I want!

I have thought of RTRIM to get rid of the spaces after and then replace, I
have also thought of CHARINDEX to find the first occurance of a space and
STUFF to replace it.
I have done my homework on these functions!
But I am having trouble writing such a statement,
I've never written a query which would use more then one function on one
column you see and I am getting confused!

I'll tell you what I want to do in simple steps

Replace only the first found space in a name column, but then if a name has
a middle initial that will be a problem,
Replace that with a dot.
then concatanate '@.emailaddress;@.emailaddress2' after it
so when SQLServer does the select it will bring back something like

joe.bloggs@.emailaddress;emailaddress
But I guess I'd also need joe.n.bloggs@.emailaddress;emailaddress

The data in the column looks like this at the moment

joe bloggs
But I guess there may come a time when we have
joe n bloggs, just to complicate things!

What is your advice, and how do I write a query like this
I have been playing around with it in Query Analyser but as I said I am
getting confused and need some help if you don't mind

Thanks a lot to all who reply :-)

Regards

JayneHi

Try:

SELECT CHARINDEX(SPACE(1),[String] ) AS [Position],[String],
STUFF ( [String], CHARINDEX(CHAR(32),[String] ), 1, SPACE(0) ) AS
[NewString]
FROM ( SELECT 'ABC DE FG' AS [String]
UNION ALL SELECT ' ABC DE FG'
UNION ALL SELECT ' ABC DE FG'
UNION ALL SELECT 'ABCDEFG'
) A

As an update statement

UPDATE MyTable
SET [String] = STUFF ( [String], CHARINDEX(CHAR(32),[String] ), 1,
SPACE(0) )
WHERE CHARINDEX(CHAR(32),[String] ) > 0
John

"Little PussyCat" <SPAMSPAM@.NOSPAM.com> wrote in message
news:v7ibg2-thj.ln1@.tiger.sphynx...
> Hello,
> I need to be able to replace only the first occurance of a space character
> in a column.
> Reason being is the data in the column I am trying to replace seems to
> have
> umpteen space characters after each entry, so a simple replace function
> will replace all the spaces after it with what I want!
> I have thought of RTRIM to get rid of the spaces after and then replace, I
> have also thought of CHARINDEX to find the first occurance of a space and
> STUFF to replace it.
> I have done my homework on these functions!
> But I am having trouble writing such a statement,
> I've never written a query which would use more then one function on one
> column you see and I am getting confused!
> I'll tell you what I want to do in simple steps
> Replace only the first found space in a name column, but then if a name
> has
> a middle initial that will be a problem,
> Replace that with a dot.
> then concatanate '@.emailaddress;@.emailaddress2' after it
> so when SQLServer does the select it will bring back something like
> joe.bloggs@.emailaddress;emailaddress
> But I guess I'd also need joe.n.bloggs@.emailaddress;emailaddress
> The data in the column looks like this at the moment
> joe bloggs
> But I guess there may come a time when we have
> joe n bloggs, just to complicate things!
> What is your advice, and how do I write a query like this
> I have been playing around with it in Query Analyser but as I said I am
> getting confused and need some help if you don't mind
> Thanks a lot to all who reply :-)
> Regards
> Jayne|||Little PussyCat wrote:

> Hello,
> I need to be able to replace only the first occurance of a space character
> in a column.
> Reason being is the data in the column I am trying to replace seems to
> have umpteen space characters after each entry, so a simple replace
> function will replace all the spaces after it with what I want!

Its ok, I've found a way to do it.
firstly I don't want extra spaces after each name anyway so I do

Update [tablename]
SET [columnname] = RTRIM(Columnname]

Then I just do SELECT REPLACE(ColumnName,' ','.') +
'@.emailaddress;@.emailaddress'
which replaces all of the spaces in the name column and puts a dot in and
also takes into account people's names who have a middle initial! then
appends the rest of the email address in.

Thanks anyway,

Jayne

CHARINDEX

I find that CHARINDEX does not seem to return the position of a matching
phrase when the token being searched contains punctuation after the phrase.
In other words CHARINDEX('Birthday', 'Hope you have a happy Birthday!', 0)
does NOT return the position of Birthday in the phrase!!
Please can anyone advise me on this.
Thanks
Bryan
I get 23 from both SQL 2000 SP4, and SQL 2005 SP2.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005

Charindex

I have atable where i store visitor data (from a website).
In table i have a column LanguageData
Typical entries are:
no
en-us
no,en-us
en; en-us; no
...
So i am trying to do a select where i remove every thing after the ,.
Like this:
LEFT(LanguageData, CHARINDEX(',',
REPLACE(RTRIM(LTRIM(LanguageData)), ';', ',')))
That result in
no,
en,
So i do not get:
no
en-us
no
en
Is there a way i can do a select so that i only get languagecode or the
first languagecode where the user has many (like no, en-us)
I am trying to do this so that i can join it to a table later and show the
full language name.
Best regards
TrondHi
Hi
May be one of
SELECT CASE WHEN Length > 1 THEN LEFT([language],Length-1)
ELSE [language]
END AS [language]
FROM ( SELECT CHARINDEX(',',REPLACE([language],';',','
)) AS LENGTH,
[language]
FROM (
SELECT 'no' as [language]
UNION ALL SELECT 'en-us'
UNION ALL SELECT 'no,en-us'
UNION ALL SELECT 'en; en-us; no' ) A ) B
or
SELECT LEFT([language],ISNULL(Length-1,LEN([Language]))) AS [language]
FROM (
SELECT NULLIF(CHARINDEX(',',REPLACE([language],
';',',')),0) AS LENGTH,
[language]
FROM (
SELECT 'no' as [language]
UNION ALL SELECT 'en-us'
UNION ALL SELECT 'no,en-us'
UNION ALL SELECT 'en; en-us; no' ) A ) B
You may also want to check out:
http://www.users.drew.edu/skass/sql...unction.sql.txt
John
"Trond" <thoiberg@.broadpark.no> wrote in message
news:42629435$1@.news.broadpark.no...
>I have atable where i store visitor data (from a website).
> In table i have a column LanguageData
> Typical entries are:
> no
> en-us
> no,en-us
> en; en-us; no
> ...
> So i am trying to do a select where i remove every thing after the ,.
> Like this:
> LEFT(LanguageData, CHARINDEX(',',
> REPLACE(RTRIM(LTRIM(LanguageData)), ';', ',')))
> That result in
> no,
> en,
> So i do not get:
> no
> en-us
> no
> en
> Is there a way i can do a select so that i only get languagecode or the
> first languagecode where the user has many (like no, en-us)
> I am trying to do this so that i can join it to a table later and show the
> full language name.
> Best regards
> Trond
>

CHARINDEX

SQL Server 2000

Ya know, it is always the simplest stuff that gets ya !!

I am having the hardest time getting a simple piece of code working.
Must be brain dead today.

Goal: Get the users full name from a string

Here is sample data:

"LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"

Code:
IF LEN(@.strReturnValue) > 0 BEGIN
SELECT @.strReturnValue = SUBSTRING(@.strReturnValue,
(CHARINDEX('CN=',@.strReturnValue)+3),
(CHARINDEX(',',@.strReturnValue)-1))
END

It will extract:
"Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"

I want it to extract:
Kevin Jones

Thanks.On 23 Jun 2005 08:51:27 -0700, csomberg@.dwr.com wrote:

> "LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> Code:
> IF LEN(@.strReturnValue) > 0 BEGIN
> SELECT @.strReturnValue = SUBSTRING(@.strReturnValue,
> (CHARINDEX('CN=',@.strReturnValue)+3),
> (CHARINDEX(',',@.strReturnValue)-1))

declare @.test varchar(255)
declare @.pos int

select @.test = 'LDAP://blahblahblah/CN=Kevin
Jones,OU=DevEng,DC=nobody,DC=priv,DC=com'

select @.pos = CHARINDEX('CN=',@.test)+3

SELECT SUBSTRING(@.test, @.pos ,(CHARINDEX(',',@.test)) - @.pos)

Tony
--
http://www.dotnet-hosting.com
Free web hosting with ASP.NET & SQL Server
No ads - No trials - Innovative features|||...oops i should have written:

You're thinking the substring syntax is
substring( string, start, end )

when the correct syntax is
substring( string, start, length )

where length is end_position - start position

Sorry for the confusion.

hth,

victor dileo

csomberg@.dwr.com wrote:
> SQL Server 2000
> Ya know, it is always the simplest stuff that gets ya !!
> I am having the hardest time getting a simple piece of code working.
> Must be brain dead today.
> Goal: Get the users full name from a string
> Here is sample data:
> "LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> Code:
> IF LEN(@.strReturnValue) > 0 BEGIN
> SELECT @.strReturnValue = SUBSTRING(@.strReturnValue,
> (CHARINDEX('CN=',@.strReturnValue)+3),
> (CHARINDEX(',',@.strReturnValue)-1))
> END
> It will extract:
> "Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> I want it to extract:
> Kevin Jones
> Thanks.|||Note the charindex syntax:
charindex ( string, start, length )

You're mistakenly thinking the syntax is:
charindex ( string, start, end )

The "end" parameter should actually be length from the "start"
position, and be calculated as something like:
length = end - start

Once you fix that, I think your code will work just fine.

hth,

victor dileo

Charindex

How can I get the value of charindex from the right side(not from the first) means from the last?
SubhasihCould you give an example please?|||USE Northwind
GO
DECLARE @.Expr1 varchar(15)
SELECT @.Expr1 = 'Way'
SELECT CASE WHEN CHARINDEX(@.Expr1, ShipAddress, 1) <> 0
THEN CHARINDEX(@.Expr1, ShipAddress, 1) + LEN(@.Expr1)
ELSE 0
END
FROM Orders
WHERE ShipAddress LIKE '%'+ @.Expr1 + '%'|||select charindex('[YourSearchCharacter]', REVERSE([YourTextString]))

blindman|||Originally posted by blindman
select charindex('[YourSearchCharacter]', REVERSE([YourTextString]))

blindman

How does that work?

USE Northwind
GO
DECLARE @.Expr1 varchar(15)
SELECT @.Expr1 = 'Way'

SELECT CHARINDEX(@.Expr1, REVERSE(ShipAddress))
FROM Orders
WHERE ShipAddress LIKE '%'+ @.Expr1 + '%'|||"How can I get the value of charindex from the right side(not from the first) means from the last?":

declare @.YourTextString varchar(100)
declare @.YourSearchCharacter char(1)
set @.YourTextString = 'For good database advice, visit DBFORUMS.COM.'
set @.YourSearchCharacter = 'V'

select charindex(@.YourSearchCharacter, REVERSE(@.YourTextString)) as ReverseCharIndex

ReverseCharIndex
------
19

I don't think subhasishray is trying to use the LIKE operator here, or trying to find the positions of substring greater than 1 character in length. A similiar statement could be developed for multi-character searches, though.

blindman|||Doesn't the statement:

How can I get the value of charindex from the right side(not from the first)

Infer multiple search chars?

The LIKE is just to minimize the sample result rows (excluses all the zeroes)

But yeah, how about (and I found a 1 byte offset bug in my original):

USE Northwind
GO
DECLARE @.Expr1 varchar(15)
SELECT @.Expr1 = 'Way'

SELECT LEN(ShipAddress)-CHARINDEX(REVERSE(@.Expr1), REVERSE(ShipAddress))+1
, CHARINDEX(@.Expr1, ShipAddress, 1) + LEN(@.Expr1) - 1
, ShipAddress
FROM Orders
WHERE ShipAddress LIKE '%'+ @.Expr1 + '%'

EDIT: Blindman, how would you do it differently?|||Oops. Correct Kaiser. I spaced the fact that Charindex can be used to search for string of more than one character.

I like this method:

declare @.YourTextString varchar(100)
declare @.YourSearchString varchar(50)
set @.YourTextString = 'For good database advice, visit DBFORUMS.COM.'
set @.YourSearchString = 'vis'

select charindex(REVERSE(@.YourSearchString), REVERSE(@.YourTextString)) + len(@.YourSearchString)-1 as ReverseCharIndex

ReverseCharIndex
------
19|||... and that fails if the search string is not found.

This is more robust, returning 0 if the search string is not found, just like the regular CHARINDEX function:

declare @.YourTextString varchar(100)
declare @.YourSearchString varchar(50)
set @.YourTextString = 'For good database advice, visit DBFORUMS.COM.'
set @.YourSearchString = 'Dummy'

select isnull(nullif(charindex(REVERSE(@.YourSearchString) , REVERSE(@.YourTextString)), 0) + len(@.YourSearchString)-1, 0) as ReverseCharIndex

blindman|||Would make a handy stock function:

create function ReverseCharIndex(@.SearchString varchar(500), @.TextString varchar(500))
returns int
as
begin
return isnull(nullif(charindex(REVERSE(@.SearchString), REVERSE(@.TextString)), 0) + len(@.SearchString)-1, 0)
end

blindman|||Looks oddly familiar...

Getting cold in OooooHIo?|||Just rainy so far.

Thursday, February 16, 2012

CHARINDEX with Unicode

CHARINDEX with Unicode

Charindex to not include the delimeter question

CHARINDEX starting from the end?

CHARINDEX returns zero in TEXT column

charindex question

CHARINDEX is not working

CHARINDEX in user-defined function

charindex in user-defined function

CHARINDEX in user-defined function

charindex in user-defined function

CHARINDEX in user-defined function

CHARINDEX in CASE Within SELECT Statement

charindex faster than like operator?

CHARINDEX doesn't work

CHARINDEX and STUFF/REPLACE

CHARINDEX

Charindex

CHARINDEX

Charindex

Char and Varchar

Blog Archive

About Me