PDF rendering: Characters overlap in Verdana

On some of our RS machines the Verdana font doesn't render correctly in PDF
format, there is no spacing between the letters (all at the same location).
Other fonts like Arial render correctly. Is this a known issue? sp1 has been
installed (I know there were some issues with this before service pack 1).
--
Callidus
Software DesignerEnsure Verdana is on the server box as well as the development
computers.
Then, on the server, use the Fonts control panel to open all Verdana
font files. Then reboot the server.
This helped us with similar problem - hope it works for you!|||Thanks for your reply. This doesn't solve our problems however. Any other
suggestions?
Was it the first time you rebooted the server after installings sp1?
"Parker" wrote:
> Ensure Verdana is on the server box as well as the development
> computers.
> Then, on the server, use the Fonts control panel to open all Verdana
> font files. Then reboot the server.
> This helped us with similar problem - hope it works for you!
>|||Yes - the server had been up for a fairly long time, as I recall.
Sounds like your problem may lie elsewhere - other things to try would
be ensuring you are using Acrobat Reader 7, or simply not using the
problematic font (kludgy, I know...)|||We use Arial Narrow and ever since we went to a W2K3 box everything exports
correctly, except for PDF which substitutes Helvetica or something with weird
spacing. Did you ever get it fixed?
"Callidus" wrote:
> Thanks for your reply. This doesn't solve our problems however. Any other
> suggestions?
> Was it the first time you rebooted the server after installings sp1?
> "Parker" wrote:
> > Ensure Verdana is on the server box as well as the development
> > computers.
> >
> > Then, on the server, use the Fonts control panel to open all Verdana
> > font files. Then reboot the server.
> >
> > This helped us with similar problem - hope it works for you!
> >
> >

PDF problem

cannot see chinese characters, and fonts from "WingDings" in the pdf
generated by the Reportingservice...
what is the problem?the pdf writer in the ReportingServer?, how to solve?
thanks in advance.I also have the same problem when I try to use some symbols
with some specific symbols Font Substitution takes place while exporting to
PDF.
Ponnurangam
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:876D7E8E-2BD0-4603-B90C-3C7D803FAA9A@.microsoft.com...
> cannot see chinese characters, and fonts from "WingDings" in the pdf
> generated by the Reportingservice...
> what is the problem?the pdf writer in the ReportingServer?, how to solve?
> thanks in advance.

PDF export can't handle special characters?

I have a field on my report which needs to show an amount in Euros, so I have the Euro (â?¬) symbol in the report. This looks fine when I render the report in HTML and Excel, but when I render in PDF the text is all messed up. This also happens for some other characters with an ASCII code above 127...
Is this a known problem and how can we solve it?
Thanks,
VincentRelated posts:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a199e6c6-c2e4-40d6-a89d-3f991a02cf3e
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=be0ab01e-7550-42c1-9617-13f3fb442cef
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Vincent" <Vincent@.discussions.microsoft.com> wrote in message
news:9D40C734-F1A8-47CA-809E-2008EFD6E338@.microsoft.com...
> I have a field on my report which needs to show an amount in Euros, so I
have the Euro (â?¬) symbol in the report. This looks fine when I render the
report in HTML and Excel, but when I render in PDF the text is all messed
up. This also happens for some other characters with an ASCII code above
127...
> Is this a known problem and how can we solve it?
> Thanks,
> Vincent
>|||Thanks for the related posts. This is indeed related to the version of the Acrobat Reader (works as of version 6.0), but I have also found a workaround for people who can only use Acrobat Reader 5.0:
Avoid the usage of Arial, Courier and Times New Roman as font type, since these are the fonts that can not be displayed properly with the euro symbol. Alternative could be Arial Unicode MS, Garamond, Microsoft Sans Serif or Verdana, these all look OK when I tested this.
I will also update the other posts, since this is interesting stuff!
"Ravi Mumulla (Microsoft)" wrote:
> Related posts:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a199e6c6-c2e4-40d6-a89d-3f991a02cf3e
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=be0ab01e-7550-42c1-9617-13f3fb442cef
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Vincent" <Vincent@.discussions.microsoft.com> wrote in message
> news:9D40C734-F1A8-47CA-809E-2008EFD6E338@.microsoft.com...
> > I have a field on my report which needs to show an amount in Euros, so I
> have the Euro (ââ'¬) symbol in the report. This looks fine when I render the
> report in HTML and Excel, but when I render in PDF the text is all messed
> up. This also happens for some other characters with an ASCII code above
> 127...
> >
> > Is this a known problem and how can we solve it?
> >
> > Thanks,
> > Vincent
> >
>
>|||I am having the same problem in displaying Greek. Works fine on acrobat 6
and I am using Courier New. My problem is that I can not avoid Courier
because I need the font to be fixed font instead of True Type (I am
displaying text that need to be aligned). Is there another Fixed Type font
that will work with Greek (or special characters) for Acrobat 5?
"Vincent" wrote:
> Thanks for the related posts. This is indeed related to the version of the Acrobat Reader (works as of version 6.0), but I have also found a workaround for people who can only use Acrobat Reader 5.0:
> Avoid the usage of Arial, Courier and Times New Roman as font type, since these are the fonts that can not be displayed properly with the euro symbol. Alternative could be Arial Unicode MS, Garamond, Microsoft Sans Serif or Verdana, these all look OK when I tested this.
> I will also update the other posts, since this is interesting stuff!
> "Ravi Mumulla (Microsoft)" wrote:
> > Related posts:
> > http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a199e6c6-c2e4-40d6-a89d-3f991a02cf3e
> > http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=be0ab01e-7550-42c1-9617-13f3fb442cef
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > "Vincent" <Vincent@.discussions.microsoft.com> wrote in message
> > news:9D40C734-F1A8-47CA-809E-2008EFD6E338@.microsoft.com...
> > > I have a field on my report which needs to show an amount in Euros, so I
> > have the Euro (ââ'¬) symbol in the report. This looks fine when I render the
> > report in HTML and Excel, but when I render in PDF the text is all messed
> > up. This also happens for some other characters with an ASCII code above
> > 127...
> > >
> > > Is this a known problem and how can we solve it?
> > >
> > > Thanks,
> > > Vincent
> > >
> >
> >
> >

PDF Errors

When generating a report it looks fine on the web, but if I export it to a
pdf and then open it, if it has certain characters in it eg â?¬ when I open in
acrobat the text beyond the character is corrupted, this only happens in
acrobat v5, in v6 its OK. If I create a word document with the same
character, export it to a pdf it looks ok when viewed in V5 or V6 of acrobat.
Any ideas'
ThanksWe have made a requirement that all our users are to use Adobe Acrobat 7 to
get the correct UNICODE output - plus there are bugs in Adobe 5 & 6 that are
fixed in 7. I find Adobe 7 to load much faster as well.
=-Chris
"RS newbie" <RS newbie@.discussions.microsoft.com> wrote in message
news:AD232AE8-97A2-46AA-A47E-09B8FCC365DB@.microsoft.com...
> When generating a report it looks fine on the web, but if I export it to a
> pdf and then open it, if it has certain characters in it eg ? when I open
> in
> acrobat the text beyond the character is corrupted, this only happens in
> acrobat v5, in v6 its OK. If I create a word document with the same
> character, export it to a pdf it looks ok when viewed in V5 or V6 of
> acrobat.
> Any ideas'
> Thanks|||We are using Reporting Services to distribute multiple reports in PD
form.
We have found that most of our users don't have Acrobat version
installed on their computers, so they can't open the reports withou
upgrading, which many of them are unwilling to take the time to do.
In addition, we've found that a few of the PDF files that have bee
created are corrupt - we can't open them from Acrobat 7 or any othe
version of Acrobat. We're not sure why this is happening. We'r
able to re-run the corrupted reports and export to PDF from Repor
Manager with no problem
My questions are, is there any alternative to requiring that user
have installed Adobe 7, and can you tell me anything about corrup
PDF files being generated
Thanks|||I am having the same problem. But its not specific to PDF - happens with XLS
as well. Did you find a fix for this?
"jillpeterson" wrote:
> We are using Reporting Services to distribute multiple reports in PDF
> form.
> We have found that most of our users don't have Acrobat version 7
> installed on their computers, so they can't open the reports without
> upgrading, which many of them are unwilling to take the time to do.
>
> In addition, we've found that a few of the PDF files that have been
> created are corrupt - we can't open them from Acrobat 7 or any other
> version of Acrobat. We're not sure why this is happening. We're
> able to re-run the corrupted reports and export to PDF from Report
> Manager with no problem.
> My questions are, is there any alternative to requiring that users
> have installed Adobe 7, and can you tell me anything about corrupt
> PDF files being generated?
> Thanks!
>|||I am having the same problem with PDF and Excel arriving corrupt but work
fine rendered directly in report manager. I have not found a fix, but perhaps
someone from MS will chime in at some point. I am using an external
non-Exchange SMTP server(Communigate Pro and Ironport spam/virus scanning
appliance). I don't know if something in the mail exchange is corrupting the
MIME type or something. I'm going to look through the mail logs to see if I
find anything.
Forrest
"comet61" wrote:
> I am having the same problem. But its not specific to PDF - happens with XLS
> as well. Did you find a fix for this?
> "jillpeterson" wrote:
> > We are using Reporting Services to distribute multiple reports in PDF
> > form.
> >
> > We have found that most of our users don't have Acrobat version 7
> > installed on their computers, so they can't open the reports without
> > upgrading, which many of them are unwilling to take the time to do.
> >
> >
> > In addition, we've found that a few of the PDF files that have been
> > created are corrupt - we can't open them from Acrobat 7 or any other
> > version of Acrobat. We're not sure why this is happening. We're
> > able to re-run the corrupted reports and export to PDF from Report
> > Manager with no problem.
> >
> > My questions are, is there any alternative to requiring that users
> > have installed Adobe 7, and can you tell me anything about corrupt
> > PDF files being generated?
> >
> > Thanks!
> >
> >|||I think Im going to repost this and see if I get a response.
"forrestsjs" wrote:
> I am having the same problem with PDF and Excel arriving corrupt but work
> fine rendered directly in report manager. I have not found a fix, but perhaps
> someone from MS will chime in at some point. I am using an external
> non-Exchange SMTP server(Communigate Pro and Ironport spam/virus scanning
> appliance). I don't know if something in the mail exchange is corrupting the
> MIME type or something. I'm going to look through the mail logs to see if I
> find anything.
> Forrest
> "comet61" wrote:
> > I am having the same problem. But its not specific to PDF - happens with XLS
> > as well. Did you find a fix for this?
> >
> > "jillpeterson" wrote:
> >
> > > We are using Reporting Services to distribute multiple reports in PDF
> > > form.
> > >
> > > We have found that most of our users don't have Acrobat version 7
> > > installed on their computers, so they can't open the reports without
> > > upgrading, which many of them are unwilling to take the time to do.
> > >
> > >
> > > In addition, we've found that a few of the PDF files that have been
> > > created are corrupt - we can't open them from Acrobat 7 or any other
> > > version of Acrobat. We're not sure why this is happening. We're
> > > able to re-run the corrupted reports and export to PDF from Report
> > > Manager with no problem.
> > >
> > > My questions are, is there any alternative to requiring that users
> > > have installed Adobe 7, and can you tell me anything about corrupt
> > > PDF files being generated?
> > >
> > > Thanks!
> > >
> > >|||I am still having the problem. I ruled out the Ironport Mail
gateway...sending the mail directly to the Communigate Pro mail server. I
still end up with corrupted attachments, pdf or excel. They still work fine
when created inside the web frontend.
A regular email with an excel attachment that
works(sent from me to me) has something like this in the header
Mime-Version: 1.0
Content-Type: multipart/mixed;
boundary="=====================_694514546
==_"
This failed emails have something like
MIME-Version: 1.0
Content-Type: multipart/mixed;
boundary="--
=_NextPart_000_004A_01C5A315.F220E550"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE
V6.00.3790.1830
Which is produced by Reporting Services. I'm guessing there is something
non-standard about Microsoft's Mime encoding with this mailer. I'm not quite
sure how to fix it. There are not many mail options in the RepSvc's config.
I'm not sure what I can do on the mail server side of things. This probably
works fine with Exchange...but that doesn't help the rest of us. --Forrest
"comet61" wrote:
> I think Im going to repost this and see if I get a response.
> "forrestsjs" wrote:
> > I am having the same problem with PDF and Excel arriving corrupt but work
> > fine rendered directly in report manager. I have not found a fix, but perhaps
> > someone from MS will chime in at some point. I am using an external
> > non-Exchange SMTP server(Communigate Pro and Ironport spam/virus scanning
> > appliance). I don't know if something in the mail exchange is corrupting the
> > MIME type or something. I'm going to look through the mail logs to see if I
> > find anything.
> >
> > Forrest
> >
> > "comet61" wrote:
> >
> > > I am having the same problem. But its not specific to PDF - happens with XLS
> > > as well. Did you find a fix for this?
> > >
> > > "jillpeterson" wrote:
> > >
> > > > We are using Reporting Services to distribute multiple reports in PDF
> > > > form.
> > > >
> > > > We have found that most of our users don't have Acrobat version 7
> > > > installed on their computers, so they can't open the reports without
> > > > upgrading, which many of them are unwilling to take the time to do.
> > > >
> > > >
> > > > In addition, we've found that a few of the PDF files that have been
> > > > created are corrupt - we can't open them from Acrobat 7 or any other
> > > > version of Acrobat. We're not sure why this is happening. We're
> > > > able to re-run the corrupted reports and export to PDF from Report
> > > > Manager with no problem.
> > > >
> > > > My questions are, is there any alternative to requiring that users
> > > > have installed Adobe 7, and can you tell me anything about corrupt
> > > > PDF files being generated?
> > > >
> > > > Thanks!
> > > >
> > > >|||I've discovered the attachments work fine in Outlook...just not Eudora Pro. I
do not think it is a email server issue..I tried sending through communigate
pro and exim with the same result. I think it is a Eudora issue in that it is
not RFC compliant in understanding UTF-8 encoded emails. The MS reportsvc
docs say that the subscription mailer encodes only in UTF-8 and cannot be
changed. I found a eudora plugin that can convert the body of a message from
utf-8 to our charset but it does not seem to help with the attachment. Blogs
say Eudora may fix the problem in future releases...but Eudora Paid 6.2.3 is
not working for me yet.
"forrestsjs" wrote:
> I am still having the problem. I ruled out the Ironport Mail
> gateway...sending the mail directly to the Communigate Pro mail server. I
> still end up with corrupted attachments, pdf or excel. They still work fine
> when created inside the web frontend.
> A regular email with an excel attachment that
> works(sent from me to me) has something like this in the header
> Mime-Version: 1.0
> Content-Type: multipart/mixed;
> boundary="=====================_694514546
> ==_"
> This failed emails have something like
> MIME-Version: 1.0
> Content-Type: multipart/mixed;
> boundary="--
> =_NextPart_000_004A_01C5A315.F220E550"
> Content-Transfer-Encoding: 7bit
> X-Mailer: Microsoft CDO for Windows 2000
> Content-Class: urn:content-classes:message
> Importance: normal
> Priority: normal
> X-MimeOLE: Produced By Microsoft MimeOLE
> V6.00.3790.1830
> Which is produced by Reporting Services. I'm guessing there is something
> non-standard about Microsoft's Mime encoding with this mailer. I'm not quite
> sure how to fix it. There are not many mail options in the RepSvc's config.
> I'm not sure what I can do on the mail server side of things. This probably
> works fine with Exchange...but that doesn't help the rest of us. --Forrest
>
> "comet61" wrote:
> > I think Im going to repost this and see if I get a response.
> >
> > "forrestsjs" wrote:
> >
> > > I am having the same problem with PDF and Excel arriving corrupt but work
> > > fine rendered directly in report manager. I have not found a fix, but perhaps
> > > someone from MS will chime in at some point. I am using an external
> > > non-Exchange SMTP server(Communigate Pro and Ironport spam/virus scanning
> > > appliance). I don't know if something in the mail exchange is corrupting the
> > > MIME type or something. I'm going to look through the mail logs to see if I
> > > find anything.
> > >
> > > Forrest
> > >
> > > "comet61" wrote:
> > >
> > > > I am having the same problem. But its not specific to PDF - happens with XLS
> > > > as well. Did you find a fix for this?
> > > >
> > > > "jillpeterson" wrote:
> > > >
> > > > > We are using Reporting Services to distribute multiple reports in PDF
> > > > > form.
> > > > >
> > > > > We have found that most of our users don't have Acrobat version 7
> > > > > installed on their computers, so they can't open the reports without
> > > > > upgrading, which many of them are unwilling to take the time to do.
> > > > >
> > > > >
> > > > > In addition, we've found that a few of the PDF files that have been
> > > > > created are corrupt - we can't open them from Acrobat 7 or any other
> > > > > version of Acrobat. We're not sure why this is happening. We're
> > > > > able to re-run the corrupted reports and export to PDF from Report
> > > > > Manager with no problem.
> > > > >
> > > > > My questions are, is there any alternative to requiring that users
> > > > > have installed Adobe 7, and can you tell me anything about corrupt
> > > > > PDF files being generated?
> > > > >
> > > > > Thanks!
> > > > >
> > > > >

Pattern Matching - Searching for Numeric or Alpha or Alpha-Numeric characters in a string

Hi,

I was trying to find numeric characters in a field of nvarchar. I looked this up in HELP.

Wildcard

Meaning

%

Any string of zero or more characters.

_

Any single character.

[ ]

Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).

Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).

Nowhere in the examples below it in Help was it explicitly detailed that a user could do this.

In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:

WHERE
Gift_Date NOT LIKE "####*"

After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:

WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'

using single quotes and the % wildcard instead of Access' double quotes and * wildcard.

Just putting this out there for anybody else that is new to SQL, like me.

Regards,

Patrick Briggs,
Pasadena, CA

Patrick Briggs wrote:

In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:

WHERE
Gift_Date NOT LIKE "####*"

After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:

WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'

It is not quite the same. The LIKE pattern in TSQL will also match values like '0123A' and '1248mkfaliw' whereas the one in Access doesn't. The correct way to specify it is to do below:

WHERE Gift_Date NOT LIKE replicate('[0-9]', 4)

The replicate function just simplifies the repetition of the same pattern multiple times.

sql

PATINDEX, LIKE and escaping

Hi,
I need to scan some character data for the presence of certain "invalid"
characters. The characters I need to scan for are
!@.#$%^&*()_+={}[]|\:;"<>,/?
The PATINDEX function works so long as the ] character is omitted, e.g.
set @.s = 'foo$'
select patindex('%[!@.#$%^&*()_+={}[|\:;"<>,/?]%', @.s)
-- returns 4
Is there any way to tell PATINDEX or LIKE to treat ] as a literal character
within a character set?
Thanks,
DanielDECLARE @.s VARCHAR(20)
SET @.s = 'foo]bar['
SELECT PATINDEX( '%]%', @.s ), PATINDEX( '%[[]%', @.s )
Anith|||Surround the OPENING bracket with brackets.
set @.s = 'foo$'
select patindex('%[!@.#$%^&*()_+={}[[]|\:;"<>,/?]%', @.s)
-- returns 0
"Daniel Pratt" <kolREMOVETHISkata_is@.hotmail.com> wrote in message
news:usIkVir6FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I need to scan some character data for the presence of certain "invalid"
> characters. The characters I need to scan for are
> !@.#$%^&*()_+={}[]|\:;"<>,/?
> The PATINDEX function works so long as the ] character is omitted, e.g.
> set @.s = 'foo$'
> select patindex('%[!@.#$%^&*()_+={}[|\:;"<>,/?]%', @.s)
> -- returns 4
> Is there any way to tell PATINDEX or LIKE to treat ] as a literal
> character within a character set?
> Thanks,
> Daniel
>

PatIndex Pattern

I am attempting to use PatIndex to find characters outside of the range of
character codes 32 - 126, or in other words, find all characters in the
ranges of 0 - 31 and 127 - 255. I have written the following so far:
DECLARE @.str varchar(1000)
SET @.str = '\[%]ZNORMAL 123? 0
0
jwH1w..0 j)?0 '
SELECT
PATINDEX('%[^ !"#$%&()*+,-./0123456789:;<=>?@.ABCDEFGHIJKLMNOPQRSTUVWXYZ\
`abcdefghijklmnopqrstuvwxyz]%', @.str)
How would I include the following characters in the pattern search: '^[]
%
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1I have worked on this problem a bit in the past, and never found a way to
escape all of those characters properly in a PATINDEX pattern. The only
solution I came up with--which is somewhat suboptimal--is to write a UDF
that uses LIKE and evaluates each character in the string. LIKE has an
optional ESCAPE argument that lets this solution work:
CREATE FUNCTION EscapedPATINDEX
(
@.Pattern VARCHAR(200),
@.String VARCHAR(8000),
@.Escape CHAR(1)
)
RETURNS INT
AS
BEGIN
DECLARE @.return INT
SELECT @.return = MIN(Number)
FROM Numbers
WHERE
number >= 1
AND number <= DATALENGTH(@.String)
AND SUBSTRING(@.String, number, 1) LIKE @.Pattern ESCAPE @.Escape
RETURN (@.Return)
END
GO
--
This UDF allows you to do, e.g.:
SELECT dbo.EscapedPATINDEX('[^ \^]', '^^^c^^^', '')
--Returns 4
--
Note that this UDF requires a table of numbers. See the following link
if you don't already have one:
http://sqljunkies.com/WebLog/amacha...mbersTable.aspx
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"cbrichards" <u3288@.uwe> wrote in message news:5fcd316e41596@.uwe...
>I am attempting to use PatIndex to find characters outside of the range of
> character codes 32 - 126, or in other words, find all characters in the
> ranges of 0 - 31 and 127 - 255. I have written the following so far:
> DECLARE @.str varchar(1000)
> SET @.str = '\[%]ZNORMAL 123? 0?
> 0?
> jwH1w..0 j)?0 '
> SELECT
> PATINDEX('%[^ !"#$%&()*+,-./0123456789:;<=>?@.ABCDEFGHIJKLMNOPQRSTUVWXY
Z\
> `abcdefghijklmnopqrstuvwxyz]%', @.str)
> How would I include the following characters in the pattern search: '^[
;]%
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1|||Thanks Adam. Works great!
Adam Machanic wrote:[vbcol=seagreen]
>I have worked on this problem a bit in the past, and never found a way to
>escape all of those characters properly in a PATINDEX pattern. The only
>solution I came up with--which is somewhat suboptimal--is to write a UDF
>that uses LIKE and evaluates each character in the string. LIKE has an
>optional ESCAPE argument that lets this solution work:
>--
>CREATE FUNCTION EscapedPATINDEX
>(
> @.Pattern VARCHAR(200),
> @.String VARCHAR(8000),
> @.Escape CHAR(1)
> )
>RETURNS INT
>AS
>BEGIN
> DECLARE @.return INT
> SELECT @.return = MIN(Number)
> FROM Numbers
> WHERE
> number >= 1
> AND number <= DATALENGTH(@.String)
> AND SUBSTRING(@.String, number, 1) LIKE @.Pattern ESCAPE @.Escape
> RETURN (@.Return)
>END
>GO
>--
> This UDF allows you to do, e.g.:
>--
>SELECT dbo.EscapedPATINDEX('[^ \^]', '^^^c^^^', '')
>--Returns 4
>--
> Note that this UDF requires a table of numbers. See the following link
>if you don't already have one:
>http://sqljunkies.com/WebLog/amacha...mbersTable.aspx
>
>[quoted text clipped - 10 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1

PatIndex Pattern

I am attempting to use PatIndex to find characters outside of the range of
character codes 32 - 126, or in other words, find all characters in the
ranges of 0 - 31 and 127 - 255. I have written the following so far:
DECLARE @.str varchar(1000)
SET @.str = '\[%]ZNORMAL 123? 0?å
0?å
jûwH1øwÿ..0 j)ß©0 '
SELECT
PATINDEX('%[^ !"#$%&()*+,-./0123456789:;<=>?@.ABCDEFGHIJKLMNOPQRSTUVWXYZ\
`abcdefghijklmnopqrstuvwxyz]%', @.str)
How would I include the following characters in the pattern search: '^[]%
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1I have worked on this problem a bit in the past, and never found a way to
escape all of those characters properly in a PATINDEX pattern. The only
solution I came up with--which is somewhat suboptimal--is to write a UDF
that uses LIKE and evaluates each character in the string. LIKE has an
optional ESCAPE argument that lets this solution work:
--
CREATE FUNCTION EscapedPATINDEX
(
@.Pattern VARCHAR(200),
@.String VARCHAR(8000),
@.Escape CHAR(1)
)
RETURNS INT
AS
BEGIN
DECLARE @.return INT
SELECT @.return = MIN(Number)
FROM Numbers
WHERE
number >= 1
AND number <= DATALENGTH(@.String)
AND SUBSTRING(@.String, number, 1) LIKE @.Pattern ESCAPE @.Escape
RETURN (@.Return)
END
GO
--
This UDF allows you to do, e.g.:
--
SELECT dbo.EscapedPATINDEX('[^ \^]', '^^^c^^^', '\')
--Returns 4
--
Note that this UDF requires a table of numbers. See the following link
if you don't already have one:
http://sqljunkies.com/WebLog/amachanic/articles/NumbersTable.aspx
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"cbrichards" <u3288@.uwe> wrote in message news:5fcd316e41596@.uwe...
>I am attempting to use PatIndex to find characters outside of the range of
> character codes 32 - 126, or in other words, find all characters in the
> ranges of 0 - 31 and 127 - 255. I have written the following so far:
> DECLARE @.str varchar(1000)
> SET @.str = '\[%]ZNORMAL 123? 0?å
> 0?å
> jûwH1øwÿ..0 j)ß©0 '
> SELECT
> PATINDEX('%[^ !"#$%&()*+,-./0123456789:;<=>?@.ABCDEFGHIJKLMNOPQRSTUVWXYZ\
> `abcdefghijklmnopqrstuvwxyz]%', @.str)
> How would I include the following characters in the pattern search: '^[]%
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Thanks Adam. Works great!
Adam Machanic wrote:
>I have worked on this problem a bit in the past, and never found a way to
>escape all of those characters properly in a PATINDEX pattern. The only
>solution I came up with--which is somewhat suboptimal--is to write a UDF
>that uses LIKE and evaluates each character in the string. LIKE has an
>optional ESCAPE argument that lets this solution work:
>--
>CREATE FUNCTION EscapedPATINDEX
>(
> @.Pattern VARCHAR(200),
> @.String VARCHAR(8000),
> @.Escape CHAR(1)
>)
>RETURNS INT
>AS
>BEGIN
> DECLARE @.return INT
> SELECT @.return = MIN(Number)
> FROM Numbers
> WHERE
> number >= 1
> AND number <= DATALENGTH(@.String)
> AND SUBSTRING(@.String, number, 1) LIKE @.Pattern ESCAPE @.Escape
> RETURN (@.Return)
>END
>GO
>--
> This UDF allows you to do, e.g.:
>--
>SELECT dbo.EscapedPATINDEX('[^ \^]', '^^^c^^^', '\')
>--Returns 4
>--
> Note that this UDF requires a table of numbers. See the following link
>if you don't already have one:
>http://sqljunkies.com/WebLog/amachanic/articles/NumbersTable.aspx
>>I am attempting to use PatIndex to find characters outside of the range of
>> character codes 32 - 126, or in other words, find all characters in the
>[quoted text clipped - 10 lines]
>> How would I include the following characters in the pattern search: '^[]%
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1

PATINDEX fails to find (UK Pound Sterling symbol)

Hi all, Has anyone ever tried looking for a UK Pound sign using the PATINDEX function? It's fine for other characters but fails to find
The following code returns -1 because the function does not locate the pound symbol (it is in there!)
SELECT @.intPos = PATINDEX('%%', [my_text_field]) FROM my_table
Any suggestions?
Many thanks!I just tried a little test...

declare @.v1 varchar(10), @.v2 nvarchar(20)
select @.v1 = '100.00', @.v2 = '100.00'
select @.v1, @.v2
select PATINDEX('%%', @.v1),PATINDEX('%%', @.v2)

and got

---- -------
100.00 100.00

---- ----
1 1

do you have anymore details?|||Ah, yes your test certainly works! Your test spurred me to rey something out...Indeed I've found out why my code was not working...

It turned out that the data I was accessing contained a unc pound character amongst the text, this looked correct in my WinXP Notepad but manifested as two odd characters when viewed in sql server, that's why my search for a normal pond character was failing!

Thanks for you help!|||sometimes it's hard to see the forrest because of all the trees! Gald to help!