Parse to first non-zero

Hello,
If I have a field that contains varchar type data which looks like '00312'
How can I parse the field to just display '312' ?
Thanks!
Patricedeclare @.i varchar(5)
select @.i ='00123'
select convert(int,@.i)
Convert it to an int
http://sqlservercode.blogspot.com/
"Patrice" wrote:

> Hello,
> If I have a field that contains varchar type data which looks like '00312'
> How can I parse the field to just display '312' ?
> Thanks!
> Patrice|||Patrice,
One way...try:
SELECT CAST(<COLUMN> AS INT) FROM <TABLE>
HTH
Jerry
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:4B70A8AF-6828-4C30-9AE0-7B2DD3E8FF37@.microsoft.com...
> Hello,
> If I have a field that contains varchar type data which looks like '00312'
> How can I parse the field to just display '312' ?
> Thanks!
> Patrice|||Another way, which is a bit safer, though not tremendously:
declare @.char10 char(10)
set @.char10 = '0000001234'
select substring(@.char10, patindex('%[^0a-z]%',@.char10), len(@.char10))
I just look for the first non zero (and I tossed in letter just for kicks)
character and substring from there. No conversion needed, so if you need to
look for
'00000012BC'
it will return:
12BC
instead of crashing out.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:4B70A8AF-6828-4C30-9AE0-7B2DD3E8FF37@.microsoft.com...
> Hello,
> If I have a field that contains varchar type data which looks like '00312'
> How can I parse the field to just display '312' ?
> Thanks!
> Patrice|||In a tiered architecture, display is always done in the front end and
NEVER in the database. This more fundamental that just SQL; don't you
remember this from your first Software Engineering course?
The stinking dirty kludge is to CAST() the string to INTEGER. But
since you did not know that a column is not anything like field, you
probably have serious design problems in many place where you have
string and other data types.|||So to quote Bobby Boucher's mamma in the Waterboy, "CAST IS THE DEVIL!!!"
:-)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129761421.858642.3380@.g47g2000cwa.googlegroups.com...
> In a tiered architecture, display is always done in the front end and
> NEVER in the database. This more fundamental that just SQL; don't you
> remember this from your first Software Engineering course?
> The stinking dirty kludge is to CAST() the string to INTEGER. But
> since you did not know that a column is not anything like field, you
> probably have serious design problems in many place where you have
> string and other data types.
>|||That was Kathy Bates! I had forgotten that Movie!
But I do remember my wife and I going to some movie years ago (forgot
the title) where George Cluney as an Army Officier and Nicole Kidman as
an atomic scientist trying to find an atomic bomb. When Kidman came on
the screen we both looked at each other and said "That [atomic bomb
expert] should have been Kathy Bates!"
But getting back from the flashback, the problem with CAST() is that
host languages do not all agree on how to handle SQL data types. Doing
the CAST() in SQL to feed it to an unknown host language conversion
adds overhead and makes results lesspredictable when someone looks at
the schema to figure out what happened. A version of the copy of a
copy of a copy.. problem.|||Yeah, I don't know why didn't think to say this. I got more interested in
just doing something other than cast. The word "display" should have jogged
something in my brain.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129761421.858642.3380@.g47g2000cwa.googlegroups.com...
> In a tiered architecture, display is always done in the front end and
> NEVER in the database. This more fundamental that just SQL; don't you
> remember this from your first Software Engineering course?
> The stinking dirty kludge is to CAST() the string to INTEGER. But
> since you did not know that a column is not anything like field, you
> probably have serious design problems in many place where you have
> string and other data types.
>

ParrallelPeriod Function not working in SSAS 2005

I am trying to use the ParallelPeriod Function in a Calculated Measure to have an aggregation of last years sales Quantity to display along side the Current Years sales quantity. I have done the exact same thing in the SSAS 2000 and it worked fine. I have a separate Table for the Time dimension that contains the Date as the key along with columns for year, quarter and month. There is a date field in my fact table which is referenced to the time demension table by the date. I use the following formula:

PARALLELPERIOD([Date By Day].[the_year - Quarter - the_month - the_date].[the_year],1, [Measures].[Qty] )

This creates the LQty measure which should contain last years sales. But when I process the cube, and browse the results I get a column filled with #Vaue errors. The error message states "Members belong to different hierarchies in the ParallelPeriod function". When I browse the cube for Qty, I can see the correct information aggregated by Year then Quarter then Month. The heirarchies seem to be correct. I have tried eleminating levels in the time dimension but still get the same error.

Are there any suggestions?Not sure what your exact MDX in AS 2000 was; but according to the SQL Server 2005 BOL, the syntax for ParallelPeriod() is:

http://msdn2.microsoft.com/en-us/library/ms145500(en-US,SQL.90).aspx
>>

ParallelPeriod (MDX)

Returns a member from a prior period in the same relative position as a specified member.

Arguments

Level_Expression

A valid Multidimensional Expressions (MDX) level expression.

Numeric_Expression

A valid MDX numeric expression.

Member_Expression

A valid MDX member expression.
...
>>
So, maybe you need an expression something like:

(PARALLELPERIOD([Date By Day].[the_year - Quarter - the_month - the_date].[the_year]),
[Measures].[Qty])

parent child, display as follows - possible with SQL?

Hi there, I would like to accomplish the following, I have a table as follows:

cat_id
parent_id
cat_descr

------
Table filled as:
1, 0, Root
2, 1, Computer
3, 2, Harddisk
4, 2, Floppy drive

I would like to get a display as of:

Root
Root > Computer
Root > Computer > Harddisk
Root > Computer > Floppy drive

Thanks!yes it's possible

you need three queries in a union
select l1.cat_descr as category1
, '>'
, l2.cat_descr as category2
, '>'
, l3.cat_descr as category3
from yourtable l1
inner
join yourtable l2
on l1.cat_id = l2.parent_id
inner
join yourtable l3
on l2.cat_id = l3.parent_id
union all
select l1.cat_descr
, '>'
, l2.cat_descr
, ' '
, ' '
from yourtable l1
inner
join yourtable l2
on l1.cat_id = l2.parent_id
union all
select l1.cat_descr
, ' '
, ' '
, ' '
, ' '
from yourtable l1
order by 1,2,3,4,5|||In general you have to use recursion (Oracle "connect by", DB2/MS SQL Server Yukon - "with")

DB2 example (I added a limit by level, remove if not needed):

with
yourtable(cat_id, parent_id, cat_descr) as (values (1, 0, 'Root'), (2, 1, 'Computer'), (3, 2, 'Harddisk'), (4, 2, 'Floppy drive'))
, tree(level, id, value) as
(
select 1, cat_id, cast(cat_descr as varchar(100)) from yourtable where parent_id = 0
union all
select t2.level+1, t1.cat_id, t2.value || '>' || t1.cat_descr
from
yourtable t1, tree t2
where t1.parent_id = t2.id
and t2.level < 10
)
select value
from tree
order by level

OUTPUT:

VALUE
--------
Root
Root>Computer
Root>Computer>Harddisk
Root>Computer>Floppy drive

Parent Child hierarchy view

Is it possible with a datagrid to display a hierarchy view of a dataset
which is held in a single table that have an ID and Parent ID field
giving multiple levels of a hierarchy?
If it is possible how do we do it?
Regards
<<<Bryan>>Found a very useful url
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_structure_objects_v1_3cok.asp

parse data

I am trying to parse some logs that I have already in a table and display them in another table or even on a web page (asp). The seperator is a & and a letter (ie &U=) that corrisponds to certain parameter. Does anyone have a script or stored procedure th
at will take each paramter and put it in a seperate field?
Go to Start --> Programs --> Microsoft SQL Server --> Server Network Utility. It should list the SQL Instances that you have installed on your computer. Hope this helps.
Tea C.
"sony5689" wrote:

> I am trying to parse some logs that I have already in a table and display them in another table or even on a web page (asp). The seperator is a & and a letter (ie &U=) that corrisponds to certain parameter. Does anyone have a script or stored procedure
that will take each paramter and put it in a seperate field?

Parent Child Dimension

Hi

I have a little problem.

I build a Parent - Child dimension an I need to change the default listing of this kind of dimensions to display some information from another column than ID and ParentID.

Is there eny way to solve that?

Tanks in advance for your help.

(AS 2005; Excel 2007)

You can change this in the name column for the the parent child primary key. Check the attribute pane in the dimension editor.

Regards

Thomas Ivarsson

|||thank you Thomas for your quick answer

Parameters of SqlDataSource

Hello I need help withsetting parameters for SqlDataSource

I have a simple program. I want display date from database on MS SQLSERVER from the table USERS only for current sing on user select by his login.

I save into this variable login current user: string @.LOGIN = Context.User.Identity.Name;

I have already done with this way without SqlDataSource:

string login = Context.User.Identity.Name;SqlConnection conn1 =newSqlConnection("server=CR\\SQLEXPRESS;database=myData;integrated security=SSPI");

conn1.Open();

SqlCommand cmd1 =newSqlCommand(" SELECT IN_OUT.TIME_START, IN_OUT.TIME_END, FROM IN_OUT INNER JOIN USER ON USER.USER_ID=IN_OUT.RC_USER_ID whereUSER.LOGIN=@.LOGIN", conn1);

cmd1.Parameters.Add("@.LOGIN",SqlDbType.NVarChar, 50);

cmd1.Parameters[

"@.LOGIN"].Value = login;1.Parameters.Add("@.LOGIN",SqlDbType.NVarChar, 50);

cmd1.Parameters[

"@.LOGIN"].Value = login;

Now I don't know how to do with SqlDataSource, what I have to set in SqlDataSource1 yet

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:myConnectionString %>"ProviderName="<%$ ConnectionStrings:myConnectionString.ProviderName %>"SelectCommand="SELECT IN_OUT.TIME_START, IN_OUT.TIME_END, FROM IN_OUT INNER JOIN USER ON USER.USER_ID=IN_OUT.RC_USER_ID where USER.LOGIN=@.LOGIN"></asp:SqlDataSource>

Right click on the SqlDataSource in the design view, follow the wizard and write custom query with parameters.

bullpit

Parameters display(Urgent)

I have a reporting requirement which is mentioned below
I have three(3) parameters in my report which are
Year
Semester
Quarter
I have a text box in my "Report Layout" which should show me these parameter
values according to the following requirement.
1) If a particular year is selected say "2007" and "Semester" and "Quarter"
parameters are selected as "All" then the text box should display "2007".
2) If a particular "Year" is selected say "2007" and "Semester" parameter is
selected as say "Semester 1" and the "Quarter" parameter is selected as "All"
then the text box should display "Semester 1 2007".
3) If a particular "Year" is selected say "2007" and "Semester" parameter is
selected as say "Semester 1" and "Quarter" parameter is selected as say
"Quarter 1" then the text box should display "Quarter 1 2007".
Please help me in doing this if anybody has the solution.
Also let me know if there are any possibilities to give "radio buttons" for
the parameters in SSRS 2005.
Thanks in advance.
--
Regards,
ArvindRavishYou have to use an Expression to fill Value property of the textbox, using
string concatenation and conditional statement (like IIF or Choose).
For example :
IIf(Parameters!Semester.Count < 10, Parameters!Year & Parameters!Semester,
Parameters!Year )
--
Jean-Pierre Riehl
http://www.blog.djeepy1.net
http://www.bewise.fr
"ArvindRavish" <ArvindRavish@.discussions.microsoft.com> wrote in message
news:6A5FB99D-BBCC-4EEA-BC65-02FDCF693544@.microsoft.com...
>I have a reporting requirement which is mentioned below
> I have three(3) parameters in my report which are
> Year
> Semester
> Quarter
> I have a text box in my "Report Layout" which should show me these
> parameter
> values according to the following requirement.
> 1) If a particular year is selected say "2007" and "Semester" and
> "Quarter"
> parameters are selected as "All" then the text box should display "2007".
> 2) If a particular "Year" is selected say "2007" and "Semester" parameter
> is
> selected as say "Semester 1" and the "Quarter" parameter is selected as
> "All"
> then the text box should display "Semester 1 2007".
> 3) If a particular "Year" is selected say "2007" and "Semester" parameter
> is
> selected as say "Semester 1" and "Quarter" parameter is selected as say
> "Quarter 1" then the text box should display "Quarter 1 2007".
>
> Please help me in doing this if anybody has the solution.
> Also let me know if there are any possibilities to give "radio buttons"
> for
> the parameters in SSRS 2005.
> Thanks in advance.
> --
> Regards,
> ArvindRavish