check if field contains numeric character

How can I select all of the rows of my table for which a certain
field's value contains other characters than a-z and A-Z (non alpha
string) ?
Thank youWhere Column like '%[0-9]%'
Look up "Pattern Matching in Search Conditions" in BOL for more information.
<samuelberthelot@.googlemail.com> wrote in message
news:1148478170.488497.325220@.j33g2000cwa.googlegroups.com...
> How can I select all of the rows of my table for which a certain
> field's value contains other characters than a-z and A-Z (non alpha
> string) ?
> Thank you
>|||If it does not help ,please post your actual data + expected result
create table #t (c1 varchar(20))
insert into #t values ('cdjdfj')
insert into #t values ('cd4jdfj')
insert into #t values ('fh')
insert into #t values ('1525')
insert into #t values ('1jj')
insert into #t values ('jkk')
select * from #t where c1 like '%[0-9]%'
<samuelberthelot@.googlemail.com> wrote in message
news:1148478170.488497.325220@.j33g2000cwa.googlegroups.com...
> How can I select all of the rows of my table for which a certain
> field's value contains other characters than a-z and A-Z (non alpha
> string) ?
> Thank you
>|||found out, had to use the PATINDEX function|||Uri Dimant wrote:
> If it does not help ,please post your actual data + expected result
> create table #t (c1 varchar(20))
> insert into #t values ('cdjdfj')
> insert into #t values ('cd4jdfj')
> insert into #t values ('fh')
> insert into #t values ('1525')
> insert into #t values ('1jj')
> insert into #t values ('jkk')
>
> select * from #t where c1 like '%[0-9]%'
>
What about :
insert into test values ('!"=A3$%^&*()_')
Try this instead:
select * from #t where c1 like '%[^A-Z]%'=20
Jamie.

Check if a caractere exists in a SQL field

Hi everybody ,
I would like with a Stored procedure check if a caracter exists in a string stored in a SQL server field.
Example :
My field contains the caracters "ABCDEF" ,
I would like to check if "C" is in this string.
Tank Uselect charindex('C', 'ABCDEF') which would return 3. If the first expression is not found in the second, it returns zero.|||Thanks joan but i would like to make the expression 2 a variable wich contains my column name

Remeber the syntax must respect syntax of stored procedure

This is my stored procedure code :
@.level is the caracter i search
@.droits is the caracters contained in my field

IF (@.Level <> "nothing")
BEGIN
SELECT @.droits=droits FROM USERS WHERE UserID=@.UserID
IF NOT SELECT charindex(@.Level, @.droits)


BEGIN
SELECT Progress=2,Errormsg="Pas l'droits."
RETURN
END
SELECT @.AccessNumber = @.AccessNumber + 1
UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@.AccessNumber WHERE SessionID=@.SessionID
SELECT Progress=0,userID=@.userID
RETURN
END

can you help me please ?|||DECLARE @.pos
IF (@.Level <> "nothing")
BEGIN
SELECT @.droits=droits FROM USERS WHERE UserID=@.UserID
SELECT @.pos = charindex(@.Level, @.droits)
IF @.pos > 0
BEGIN
SELECT Progress=2,Errormsg="Pas l'droits."
RETURN
END

SELECT @.AccessNumber = @.AccessNumber + 1
UPDATE USERLOG SET Accessdt=GETDATE(), AccessNumber=@.AccessNumber WHERE SessionID=@.SessionID
SELECT Progress=0,userID=@.userID
RETURN
END|||It works

Thank U guy : ))

Charts - too much data - possible to break up into 2 charts

If the user is reporting on a lot of information then the line chart/graph
in the report contains too much data and the X-Axis labels just look like
one big blob because all of the item labels are squeezed together too
tightly.
Has anyone found a way to say for example... If there are more than X number
of items on the X Axis in the chart then to split the chart into 2 charts.
The second chart would appear directly beneath the 1st chart. This way the
user would be able to telll what the data is they are looking at.
This seems to be a common problem in all charting situations and the
solution usually is just filtering on less data. I don't see why we have to
tell the user to filter on less data and run multiple reports. It seems
possible that en enterprise reporting application like RS should have this
capability... or... the capability to code it in the report.
Any ideas?There is no perfect solution for this. Some ideas:
* You could use filters and multiple charts and use conditional visibility
to hide unneeded charts
* Group the data and use drillthrough reports with charts showing the
details per group
* If you use a bar chart visualization instead of line chart, you could use
a table with groupings. In the group header you would add a chart. The more
data you have in the dataset, the more groups you get and the more chart
bars are drawn. A sample for this approach is copied to the bottom of this
posting.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"D Witherspoon" <dwitherspoon@.noway.org> wrote in message
news:elaCN4ivFHA.2008@.TK2MSFTNGP10.phx.gbl...
> If the user is reporting on a lot of information then the line chart/graph
> in the report contains too much data and the X-Axis labels just look like
> one big blob because all of the item labels are squeezed together too
> tightly.
> Has anyone found a way to say for example... If there are more than X
> number of items on the X Axis in the chart then to split the chart into 2
> charts. The second chart would appear directly beneath the 1st chart.
> This way the user would be able to telll what the data is they are looking
> at.
> This seems to be a common problem in all charting situations and the
> solution usually is just filtering on less data. I don't see why we have
> to tell the user to filter on less data and run multiple reports. It
> seems possible that en enterprise reporting application like RS should
> have this capability... or... the capability to code it in the report.
>
> Any ideas?
====
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table2">
<Height>0.75in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Product Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>0-200</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<DataSetName>Northwind</DataSetName>
<Top>0.125in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.5in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ProductName_2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>ProductName_2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ProductName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Chart Name="chart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
<Legend>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitsInStock.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker />
</DataPoint>
</DataPoints>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitsOnOrder.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker />
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title />
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<Min>0</Min>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>0</PointWidth>
<Type>Bar</Type>
<Title />
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Units In Stock</Label>
</StaticMember>
<StaticMember>
<Label>Units On Order</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Stacked</Subtype>
<PlotArea>
<Style>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Max>200</Max>
<Margin>true</Margin>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table2_Group1">
<GroupExpressions>
<GroupExpression>=Fields!ProductName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>1.33333in</Width>
</TableColumn>
<TableColumn>
<Width>3.5in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.25in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>99dae70f-00e4-4971-a2f6-486f3896814a</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>4.83333in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="ProductID">
<DataField>ProductID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="ProductName">
<DataField>ProductName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="SupplierID">
<DataField>SupplierID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CategoryID">
<DataField>CategoryID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="QuantityPerUnit">
<DataField>QuantityPerUnit</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="UnitsInStock">
<DataField>UnitsInStock</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="UnitsOnOrder">
<DataField>UnitsOnOrder</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="ReorderLevel">
<DataField>ReorderLevel</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="Discontinued">
<DataField>Discontinued</DataField>
<rd:TypeName>System.Boolean</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>select * from products</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<PageHeight>100in</PageHeight>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>9ecf615a-64ce-4ec1-98bf-05333c406eb6</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

Charting and the Document Map

Hi,
I have a report which contains 3 different charts. What I need to do is have
all 3 of the chart names in the Document and only 1 chart showing at a time.
The users can then click on a different chart name in the Document Map to
view a different chart.
Is this possible and any suggestions?
ThanksThe document map cannot be used to dynamically "show/hide" report items. It
can only be used to navigate through the report.
If the report only contains charts, you could add page breaks between the
charts and therefore have 3 pages and clicking in the document map would
just navigate the pages of the report. Not sure if that would work in your
situation.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Spencer23" <Spencer23@.discussions.microsoft.com> wrote in message
news:0C181E97-D056-495C-829D-94D78E73FFB6@.microsoft.com...
> Hi,
> I have a report which contains 3 different charts. What I need to do is
> have
> all 3 of the chart names in the Document and only 1 chart showing at a
> time.
> The users can then click on a different chart name in the Document Map to
> view a different chart.
> Is this possible and any suggestions?
> Thanks

chart series

Hi,
I have create a report which contains a chart.
The series gets populated with the correct data but at the end of each
series, there is a text "Series 1" joined to the end of the actual text.
How is it possible to make sure the word "Series 1" does not appear at the
end of each series line?
ThanksProblem solved by restarting the machine.
"farshad" wrote:
> Hi,
> I have create a report which contains a chart.
> The series gets populated with the correct data but at the end of each
> series, there is a text "Series 1" joined to the end of the actual text.
> How is it possible to make sure the word "Series 1" does not appear at the
> end of each series line?
> Thanks

Chart in Subreport in Table - wrong data is charted

I have a report that contains a single chart. That report works fine by
itself, and as a subreport in many other contexts, but...
When I use that report as a sub-report inside a table, passing all of the
parameters to the sub-report based on values from the current row of the
table...
The chart is labelled correctly (I echo all parameters in the chart title),
but the data that's charted is clearly wrong. The chart always has 3
series, but in any given table, only the first chart is correct. All the
rest "randomly" (but reproducibly) either show all zeros for one or more
(but never all) series, or show data that looks a lot like the rendered
version of the data from the first chart (i.e. the data is the same
magnitude and shape on the printed page even if the real magnitude is quite
different).
I'm sure this is some magic of RS trying to "help" me by "grouping" or
"filtering" or in some other way "fixing" my data, since clearly I didn't
want to simply see the results of my queries charted... but I'm at a loss to
know what to do about it.
Any ideas?
I'm using RS 2005 SP2 on Server 2003 R2 SP2 x64.
-cd"Carl Daniel [VC++ MVP]" <cpdaniel_remove_this_and_nospam@.mvps.org.nospam>
wrote
>I have a report that contains a single chart. That report works fine by
>itself, and as a subreport in many other contexts, but...
> When I use that report as a sub-report inside a table, passing all of the
> parameters to the sub-report based on values from the current row of the
> table...
> The chart is labelled correctly (I echo all parameters in the chart
> title), but the data that's charted is clearly wrong. The chart always
> has 3 series, but in any given table, only the first chart is correct.
> All the rest "randomly" (but reproducibly) either show all zeros for one
> or more (but never all) series, or show data that looks a lot like the
> rendered version of the data from the first chart (i.e. the data is the
> same magnitude and shape on the printed page even if the real magnitude is
> quite different).
> I'm sure this is some magic of RS trying to "help" me by "grouping" or
> "filtering" or in some other way "fixing" my data, since clearly I didn't
> want to simply see the results of my queries charted... but I'm at a loss
> to know what to do about it.
> Any ideas?
> I'm using RS 2005 SP2 on Server 2003 R2 SP2 x64.
PS: I can see in SQL Profiler that all of the correct queries are being
executed with the correct parameters - one outer query by the master report
and one query per row for the sub report, with parameters taken from the
results of the outer query. All queries are OLAP/MDX queries, in case that
makes any difference (I can't imagine how it could, but who knows).
PPS: Yes, I know this would be more efficient if I "simply" built a larger
query and used grouping in a list or table instead of a sub-report, but
that's not the point. The queries are complex, and composing them would be
challenging (at least!); plus, the number of rows from the outer query is
never large.
-cd

Chart expression error

I have a pretty simple chart which seems to work, but when I build it, I get
a warning that "the expression for the chart "chart1" contains an error:
operation is not valid due to the current state of the object". I've
experimented with a bunch of things to get rid of the warning,
unsuccessfully. May I have some suggestions for how to figure out exactly
what the warning is referencing?
--
Bob RogersBob, can you post a minimal RDL running against e.g. Northwind which
reproduces the issue. This would help in narrowing down and finding a
solution.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob Rogers" <BobRogers@.discussions.microsoft.com> wrote in message
news:11DF0CF7-5EE9-415E-934B-7F407604AFD2@.microsoft.com...
> I have a pretty simple chart which seems to work, but when I build it, I
get
> a warning that "the expression for the chart "chart1" contains an error:
> operation is not valid due to the current state of the object". I've
> experimented with a bunch of things to get rid of the warning,
> unsuccessfully. May I have some suggestions for how to figure out exactly
> what the warning is referencing?
> --
> Bob Rogers|||I did finally discover the problem: an error in an expression controlling the
"data label" within "point labels" accessed through the Data tab of a chart
property. There was no misbehavior apparent, so I just had to plug through
all the specifications. That was my first chart. Next time, I will pay
close attention to the appearance of a warning, while I still have some idea
what I had just done that might have caused it. I was hoping you would say
"turn on x debug option and read a stack" or something like that. Anyway,
thanks for the attempt.
"Robert Bruckner [MSFT]" wrote:
> Bob, can you post a minimal RDL running against e.g. Northwind which
> reproduces the issue. This would help in narrowing down and finding a
> solution.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Bob Rogers" <BobRogers@.discussions.microsoft.com> wrote in message
> news:11DF0CF7-5EE9-415E-934B-7F407604AFD2@.microsoft.com...
> > I have a pretty simple chart which seems to work, but when I build it, I
> get
> > a warning that "the expression for the chart "chart1" contains an error:
> > operation is not valid due to the current state of the object". I've
> > experimented with a bunch of things to get rid of the warning,
> > unsuccessfully. May I have some suggestions for how to figure out exactly
> > what the warning is referencing?
> > --
> > Bob Rogers
>
>

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

Character count limit?

Does anyone know what the character count limit is for a SQL table? We have a table with a column called body_text. It contains characters from a story we publish every day. Example:

News from Canada and Turkey

We get our weekly news update from

our Canadian correspondent, Vaughn Palmer. Plus, the Pope is in Turkey.

The visit is provoking opposition from secular nationalists and

Islamists. We talk to a reporter in Istanbul. Also, our weekly

listener's forum.

I did a word count in MS Word. This paragraph is 275 characters long. I found this in the only SQL book I currently have called Beginning SQL Server 2005. I found a snippet I thought might be related to my question.

Char

The char data type is fixed in length. If you define a

column to be 20 characters long, then 20 characters will be stored. If you

enter less then the number of characters defined, the remaining length will be

space filled to the right. Therefore, if a column were defined as char (10),

"aaa" would be stored as "aaa

". Use the data type when the column data is to be of fixed length,

which tends to be the case for customer IDs and bank account IDs.

So what is the official answer?

Assuming that your actual question is:

"Does anyone know what the character count limit is for a SQL table?"

In Sql Server 2k the limit for in-row-data is 8060. if you want to store more than that you will have to use a data type which is stored off-row like text.
In SQL 2k the limit for in row-data is also 8060, but you can take advantage of tnew data types which extend the VARCHAR /NVARCHAR types to a max of 4GB/2GB.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Cool thanks!