How to format numerics with the sign on the right?

I've got an amount field in a report where I want the values to have the sign
(if negative) on the right. But I also want the numbers to line up properly.
I can't seem to find a way to format this. In Excel, you'd use a format like
#,##0.00_);#,##0.00-. But that does not quite do it in Reporting Services. I
see you can use custom formats and the syntax is similar to Excel but not
quite the same. I tried:
#,##0.00 ;#,##0.00-
but RS seems to ignore the whitespace. I also tried:
#,##0.00' ';#,##0.00-
also to no avail.
Any suggestions?What about using double quotes: " " ?
"virtualfergy" wrote:
> I've got an amount field in a report where I want the values to have the sign
> (if negative) on the right. But I also want the numbers to line up properly.
> I can't seem to find a way to format this. In Excel, you'd use a format like
> #,##0.00_);#,##0.00-. But that does not quite do it in Reporting Services. I
> see you can use custom formats and the syntax is similar to Excel but not
> quite the same. I tried:
> #,##0.00 ;#,##0.00-
> but RS seems to ignore the whitespace. I also tried:
> #,##0.00' ';#,##0.00-
> also to no avail.
> Any suggestions?|||Tried it. No luck I'm afraid. Works the same as single quotes. Any other
advice out there?
"Albert" wrote:
> What about using double quotes: " " ?
> "virtualfergy" wrote:
> > I've got an amount field in a report where I want the values to have the sign
> > (if negative) on the right. But I also want the numbers to line up properly.
> > I can't seem to find a way to format this. In Excel, you'd use a format like
> > #,##0.00_);#,##0.00-. But that does not quite do it in Reporting Services. I
> > see you can use custom formats and the syntax is similar to Excel but not
> > quite the same. I tried:
> >
> > #,##0.00 ;#,##0.00-
> >
> > but RS seems to ignore the whitespace. I also tried:
> >
> > #,##0.00' ';#,##0.00-
> >
> > also to no avail.
> >
> > Any suggestions?

How to format numbers in SQL Query

any body have an idea abou how to wirte a function for formating a numeric field.
Ex: In my table the TotalAmout is a numeric field. if i use (Select TotalAmount from Table1) then query will return numbers like

Totalamount
-------
12232.88
23233.22
23559.99
32434.99

but i want he result like comma separated format
like

12,232.88
23,233.22
23,559.99
32,434.99Create the below function and use it as said below.

/*This function is only for thousand separator for numbers with length 5 or 4*/
CREATE FUNCTION DBO.SEPARATETHOUSANDNUM
(
@.STRVALUE VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN

DECLARE @.STRRETURNVALUE VARCHAR(8000)
SELECT @.STRRETURNVALUE = CASE LEN(@.STRVALUE)
WHEN 5 THEN LEFT(@.STRVALUE,2)+ ','+ RIGHT(@.STRVALUE,3)
WHEN 4 THEN LEFT(@.STRVALUE,1)+ ','+ RIGHT(@.STRVALUE,3)
ELSE @.STRVALUE END
RETURN @.STRRETURNVALUE
END

SELECT DBO.SEPAREATENUMBERS(23565) AS CHANGEDCOLUMN

gives 23,565

SELECT DBO.SEPAREATENUMBERS(2365) AS CHANGEDCOLUMN

gives 2,365

So use
Select DBO.SEPARATETHOUSANDNUM(TotalAmount) from Table1

Quote:

Originally Posted by sukeshchand

any body have an idea abou how to wirte a function for formating a numeric field.
Ex: In my table the TotalAmout is a numeric field. if i use (Select TotalAmount from Table1) then query will return numbers like

Totalamount
-------
12232.88
23233.22
23559.99
32434.99

but i want he result like comma separated format
like

12,232.88
23,233.22
23,559.99
32,434.99

|||I got an another easy solution for that and no need for any functions

like this

select convert(varchar(50),convert(momey,TotalAmount),1) from BillMaser|||

Quote:

Originally Posted by sukeshchand

I got an another easy solution for that and no need for any functions

like this

select convert(varchar(50),convert(momey,TotalAmount),1) from BillMaser

Excellent! Thanks for posting the solution!|||

Quote:

Originally Posted by sukeshchand

I got an another easy solution for that and no need for any functions

like this

select convert(varchar(50),convert(money,TotalAmount),1) from BillMaser

i'm using this query, it works, but my problem now is that this query automatically rounds to 2 decimal places.

ex.
1200.114 = 1,200.11

is there a query that formats the result but does not round the decimals?|||

Quote:

Originally Posted by mjv

i'm using this query, it works, but my problem now is that this query automatically rounds to 2 decimal places.

ex.
1200.114 = 1,200.11

is there a query that formats the result but does not round the decimals?

try adding precision on your convert function.

actually, although this is feasible in the database/back-end, i believe this can be better be handled in the front-end.

how to format in to dd/mm/yyyy ?

hi all,

i have table field name call

Start_date varchar(16)

when i select data from that filed values it gives me

Eg:

select Start_date from Customer

20011224 00:00:0
20011004 00:00:0

but i want to convert this data in to dd/mm/yyyy format ?

like ! 24/12/2001

04/10/2001

how do i do this task ?

regards

sujithf

create table #format (

start_Date_time varchar(16)

)

insert into #format values('20011224 00:00:0')

select convert(varchar(16), cast(start_date_time as datetime), 103) from #format

--103 is a British/French date format "dd/mm/yyyy"

|||

thanks very much.....

regards

sujithf

How to format in SQL

Hi All,

I have a serial number field in table. Field type is integer. It is just stored as 1,2,3,12,13, etc.

It is showing as 00001,00002,00003,00012,00013 in interface. C# string format is very easy to changed the format.

But when i export to excel there is a problem. Let me know how to format string in SQL and export to excel.

Thanks

Aung

Hi Aung,

You may want to try this

select right('00000'+cast(serialno as varchar(5)),5) from table

|||

select right('0000'+cast(serialno as varchar(5)),5) from table

should work.

Or you can use:

SELECTRIGHT('0000'+CONVERT(VARCHAR(5),serialno),5) FROM yourTable

|||

Thanks BRO...

This is what I want.

How to format Field ?

HOw to set the field (Password) in sqlServer database to '*' .
For example :
I key in VB : 123
In SqlServer database should appear : ***
Yoir help will be appreaciated.
daniel.Daniel:
I don't think that this is a datawarehouse question, but nevertheless
In SQLServer it is not possible to individually mask (as in MS Access) or
encrypt a single column as such. You would have to write your own encryption
routine or get one (many are available).
I usually encrypt my passwords and also set proper user rights restrictions
in order to protect the passwords.
Balaji Vasudevan
"Daniel" <anonymous@.discussions.microsoft.com> wrote in message
news:E11D06A0-335A-481D-8A7B-7D32FE0F6E9D@.microsoft.com...

quote:

---

> HOw to set the field (Password) in sqlServer database to '*' .
> For example :
> I key in VB : 123
> In SqlServer database should appear : ***
> Yoir help will be appreaciated.
> daniel.

---

How to format DateTime field in Crystal

I have a DateTime field but I only want to show the date. I can't find the function to do that.
Help!!
johnright click, format field, date and time tab, customize, date and time tab, choose 'date' in Order dropdown, then use date tab to choose format.

How to format a number field..

i have a number field but i can't seem to make a format out of it..
e.g.
when i have a value of 1, i want to make it appear in the report like 0000000001 or if i have 11, i want to make it appear like 0000000011
Hope someone could help!
thanksHello DarylAps,

You have two options :

1. While selecting records from table itself use replicate function (assuming you are using mssql server ) and return formatted number.
or
else

2. Create a formula as shown below : assuming your numeric field name is col :

ReplicateString('0', 10-Length ({SqlCommand.col} ) )+{SqlCommand.col}

Here are the details of ReplicateString functions of CR :

ReplicateString (str, #copies)
Basic and Crystal syntax.

Arguments
str is the text string to be replicated.
#copies is a whole number indicating the number of times str is to be replicated.

I hope you like this resolution.

Thanks
Dilemma

How to format a datetime field?

Hi,
I have a datetime field SDate with a value '1/1/02'. I want to display it
as 01/01/2002 in a view. So I use this statement:
SELECT CONVERT(datetime, SDate,101) from Table1.
But it still display it as 1/1/02. The Help says 101 will display yyyy if I
use it with CONVERT.
Thanks.declare @.t datetime
set @.t=getdate()
select convert(char(10),@.t,101)
"Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
news:OYpFl5yUFHA.548@.tk2msftngp13.phx.gbl...
> Hi,
> I have a datetime field SDate with a value '1/1/02'. I want to display it
> as 01/01/2002 in a view. So I use this statement:
> SELECT CONVERT(datetime, SDate,101) from Table1.
> But it still display it as 1/1/02. The Help says 101 will display yyyy if
> I use it with CONVERT.
> Thanks.
>
>|||Thanks a lot. I replaced datetime with char(10) and it works.
"Farmer" <someone@.somewhere.com> wrote in message
news:eRMn27yUFHA.548@.tk2msftngp13.phx.gbl...
> declare @.t datetime
> set @.t=getdate()
> select convert(char(10),@.t,101)
>
> "Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
> news:OYpFl5yUFHA.548@.tk2msftngp13.phx.gbl...
>|||Note of course that when you format it this way that it is no longer a date
value, it is a character value. May not be a problem for you, but it could
be confusing in how it gets used by a client program. and if you want to
sort by it, since it will sort on month first (assuming you are American!)
then day, then year.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
news:%23XAycCzUFHA.1552@.TK2MSFTNGP10.phx.gbl...
> Thanks a lot. I replaced datetime with char(10) and it works.
> "Farmer" <someone@.somewhere.com> wrote in message
> news:eRMn27yUFHA.548@.tk2msftngp13.phx.gbl...
>|||I think the best way, on the backend and on the client application is to
store the value in ISO format, so you need not mess up with cutting the
time, formatting from one pattern to another.
Just my two pence and experience within projects.
Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> schrieb im Newsbeitrag
news:OihPLOzUFHA.3176@.TK2MSFTNGP12.phx.gbl...
> Note of course that when you format it this way that it is no longer a
> date value, it is a character value. May not be a problem for you, but it
> could be confusing in how it gets used by a client program. and if you
> want to sort by it, since it will sort on month first (assuming you are
> American!) then day, then year.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
> news:%23XAycCzUFHA.1552@.TK2MSFTNGP10.phx.gbl...
>

How to format a date field in select query

Is it possible to format the date field create_date (mm/dd/yyyy or mm/dd/yy)
I use the following query in stored proc. will be called in the asp.net page for population the datagrid.

select id, name, create_date from actionstable;

Please help, Thank you.You can use the SQL CONVERT() function, to convert the date to an nvarchar(), or better, format the date in the presentation layer, in the datagrid itself. In the DataFormatString of the DataGrid's column that will contain the date, use 0:d

How to format a date field

Hi,
comming from php/mysql some things here on this side are great - but some seems to be solved in a way I can not figure out.
What I need is a way to get a string in the format "yyyy-mm" out of a date-time field like:

09/05/2006 23:12:36 ??should produce ???2006-09 ???as one string

What I figured out by my own is:

SELECT { fn CONCAT({ fn CONCAT(DATENAME(yyyy, dateField), '-') }, STR(DATEPART(mm, dateField))) }, ...

but this returns "2006- ???9" with blanks in it. Or I could use 2 times the DATENAME but this would give 2006-September.
Would it help to use a stored procedure?
Thanks,
Klaus

in SQL, you could do the conversion like this:

declare @.das datetime set @.d =getdate()printconvert(varchar(7),@.d, 20)--convert to yyyy-mm-dd hh:mi:ss but only keep the left 7 chars

my preference is to return dates from sql intact, and then format them at the presentation layer - like this

Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.LoadDim dAs DateTime =Date.Now Response.Write(d.ToString("yyyy-MM"))End Sub

sql

How to find the second largest value in a field !

Hi,

i am taking the values from four tables ,

I am showing the Salesman in the descending order. according to their Sale Amount. by displaying in Descending, user can able to view the
salesman who sold for the highest amount.

Now I want to find the second highest Amount in the field.

for the highest and lowest we can use the Max and Min funtion.

for the second highest value, How can I write the query.

I already check the previous forums. But i couldn't get the idea.

Kindly reply me

Thank you very much,
Chock.Originally posted by chock
Hi,

i am taking the values from four tables ,

I am showing the Salesman in the descending order. according to their Sale Amount. by displaying in Descending, user can able to view the
salesman who sold for the highest amount.

Now I want to find the second highest Amount in the field.

for the highest and lowest we can use the Max and Min funtion.

for the second highest value, How can I write the query.

I already check the previous forums. But i couldn't get the idea.

Kindly reply me

Thank you very much,
Chock.
Well one way would be to say: what is the highest value after the highest value has been excluded (if you follow me):

SELECT MAX(amount)
FROM mytab
WHERE amount != (SELECT MAX(amount) FROM mytab);

Of course, you wouldn't want to use this recursive approach to get the 5th highest amount! For that, you could do:

SELECT amount FROM mytab m1
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab m2
WHERE m2.amount > m1.amount
);

i.e. get the amount for which there are exactly 4 higher amounts in the table.|||Hi,

You send me two queries, the first query I understand it. But in the second query

SELECT amount FROM mytab m1
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab m2
WHERE m2.amount > m1.amount
);
what's m1 and what's m2. In the previous query you didn't use the m1.

Actually Amount is the Field name we are going to compare and select.
and mytab is the Table Name.
I am new to this so I think i need some more o understand. can you please tell about the m1 and m2.

Thank you very much,
Chock.|||Originally posted by chock
Hi,

You send me two queries, the first query I understand it. But in the second query

SELECT amount FROM mytab m1
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab m2
WHERE m2.amount > m1.amount
);
what's m1 and what's m2. In the previous query you didn't use the m1.

Actually Amount is the Field name we are going to compare and select.
and mytab is the Table Name.
I am new to this so I think i need some more o understand. can you please tell about the m1 and m2.

Thank you very much,
Chock.
m1 and m2 are "aliases". I made them up, because I wanted to use the same table "mytab" twice in the same query and compare values. Without aliases the query would be:

SELECT amount FROM mytab
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab
WHERE mytab.amount > mytab.amount
);

... which will return no data, because the condition "WHERE mytab.amount > mytab.amount" is nonsense. What I want to say is "WHERE mytab.amount (in this subquery) > mytab.amount (in the main query)". Aliases allow you to do that.|||tony, you may have confused the issue by jumping from the second highest to the fifth

here's another way to get the row with the second highest value:select Salesman, SaleAmount
from SalesTable
where SaleAmount =
( select max(SaleAmount)
from SalesTable
where SaleAmount <
( select max(SaleAmount)
from SalesTable
)
)in english, "get the row where the SaleAmount is the highest SaleAmount that is less than the highest overall SaleAmount"

wouldn't want to nest that too deeply, eh

i believe a good optimiser will evaluate the innermost first (it is not correlated), then the next inner, then do a straight retrieval -- i could be wrong, though (it has happened, and optimizer performance is not my long suit)

rudy
http://r937.com

How to find the rowcount of the dataset in a report

I have a report.

In one of the text field i have to show the rowcount of the dataset

How can i do that

I dont understand what exactly you mean. If you want the total count/number of rows in a dataset, use CountRows("Dataset1"). If you want to get the row number of each row in the dataset (in a list or a table), then use RowNumber("Dataset1")

Shyam

|||

Thanks Shyam

CountRows("Dataset1"). is my solution

sql

How to find Sum on the summary field

Hi,

I need to find the grand total of a feild in the report ,which is already a summary field. How do i find the grand total of that field. any help could be appreaciable.

For example

No of Days
3.456
4.678
2.874
1.568...

I need to find the Sum(No of days). But no of days is already a summary field

ThanksTry using the Insert menu and choose 'Grand Total'. It should automatically place it in the footer of the report.

Hope this helps!
Jules|||I tried it to do like that but as it is alreadya calculated and summary filed it is showing that field in insert summary options. Anyway thanks for your advice.
Thanks
Sudharsan|||I guess I am not totally understanding here...

What is the summary not showing you?

Jules|||Okay, I went back and re-read what you wrote.

You have a summary field that pulls information in and gives you a summary of each individual day, by the day, right?

If so, you are only summarizing here, not doing a grand total (which you know). Did you go to the Insert menu at the top of the screen and go down to 'Grand Total'? It is right above 'Summary' there.

Jules|||Hi
Here i am attching the report for your reference. I want the grand total on the Average # of buss days field. Could you Please suggest me what to do.|||It won't let me open the report - it says that it is an invalid version. What version do you have?

I have a different version on my computer at home (I'm at work) so I can try it there...

Jules|||That report was build on Crystal 11

How to find record with xml data containing value...

Hello all,
SQL2005
I am newbe in XPath and XQuery
I have to find in table with xml field all records where value of attribute
contains string (e.g person name)
drop table dbo.bbb;
create table dbo.bbb(
p1 int not null identity,
p2 xml,
primary key(p1) );
insert into dbo.bbb values
('<root><item werte="d1"/><item werte="a2"/></root>');
insert into dbo.bbb values
('<root><item werte="b1"/><item werte="b2"/></root>');
select *
from dbo.bbb
where p2.value('contains((/root/item/@.werte)[1],"a")','bit') = 1
this query give me records with @.werte containing "a" but only if it is in
first item,
I have to find records with @.werte containing "a" regardless of item
position.
Can anybody help me write this query?
Regards
YaroOK, I know
select *
from dbo.bbb
where
p2.exist('/root/item/@.werte[contains(.,"a")]') = 1
Yaro
Uytkownik "Yaro" <yarok_delthisdes_@.op.pl> napisa w wiadomoci
news:e12vcm$nq4$1@.83.238.170.160...
> Hello all,
> SQL2005
> I am newbe in XPath and XQuery
> I have to find in table with xml field all records where value of
> attribute contains string (e.g person name)
> drop table dbo.bbb;
> create table dbo.bbb(
> p1 int not null identity,
> p2 xml,
> primary key(p1) );
> insert into dbo.bbb values
> ('<root><item werte="d1"/><item werte="a2"/></root>');
> insert into dbo.bbb values
> ('<root><item werte="b1"/><item werte="b2"/></root>');
> select *
> from dbo.bbb
> where p2.value('contains((/root/item/@.werte)[1],"a")','bit') = 1
> this query give me records with @.werte containing "a" but only if it is
> in first item,
> I have to find records with @.werte containing "a" regardless of item
> position.
> Can anybody help me write this query?
> Regards
> Yaro

How to find record with xml data containing value...

Hello all,
SQL2005
I am newbe in XPath and XQuery
I have to find in table with xml field all records where value of attribute
contains string (e.g person name)
drop table dbo.bbb;
create table dbo.bbb(
p1 int not null identity,
p2 xml,
primary key(p1) );
insert into dbo.bbb values
('<root><item werte="d1"/><item werte="a2"/></root>');
insert into dbo.bbb values
('<root><item werte="b1"/><item werte="b2"/></root>');
select *
from dbo.bbb
where p2.value('contains((/root/item/@.werte)[1],"a")','bit') = 1
this query give me records with @.werte containing "a" but only if it is in
first item,
I have to find records with @.werte containing "a" regardless of item
position.
Can anybody help me write this query?
Regards
Yaro
OK, I know
select *
from dbo.bbb
where
p2.exist('/root/item/@.werte[contains(.,"a")]') = 1
Yaro
Uytkownik "Yaro" <yarok_delthisdes_@.op.pl> napisa w wiadomoci
news:e12vcm$nq4$1@.83.238.170.160...
> Hello all,
> SQL2005
> I am newbe in XPath and XQuery
> I have to find in table with xml field all records where value of
> attribute contains string (e.g person name)
> drop table dbo.bbb;
> create table dbo.bbb(
> p1 int not null identity,
> p2 xml,
> primary key(p1) );
> insert into dbo.bbb values
> ('<root><item werte="d1"/><item werte="a2"/></root>');
> insert into dbo.bbb values
> ('<root><item werte="b1"/><item werte="b2"/></root>');
> select *
> from dbo.bbb
> where p2.value('contains((/root/item/@.werte)[1],"a")','bit') = 1
> this query give me records with @.werte containing "a" but only if it is
> in first item,
> I have to find records with @.werte containing "a" regardless of item
> position.
> Can anybody help me write this query?
> Regards
> Yaro

How to find out which field in a table is updated

When someone modifies a field in a table, I like to find out which
field he/she changed.
Currently, only the LAST person to modify an object is saved. For
example, if I modify an Application record, I will see "MyName" in the
"tblApplications.UpdatedBy" field and the date and time I updated it.
But it doesn't keep an historical record. We would like these changes
to be stored in a file or a table or something.
Please helpThen implement triggers to log to an audit table the changes to a data table

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"lphuong" <lphuong@.neh.gov> wrote in message
news:b6a732a8.0404300532.30de1e3c@.posting.google.c om...
> When someone modifies a field in a table, I like to find out which
> field he/she changed.
> Currently, only the LAST person to modify an object is saved. For
> example, if I modify an Application record, I will see "MyName" in the
> "tblApplications.UpdatedBy" field and the date and time I updated it.
> But it doesn't keep an historical record. We would like these changes
> to be stored in a file or a table or something.
> Please help|||Allan, would you please show me how to do it in SQL Enterprise or in
VB6. I'm a novice in this subject.
Thank you.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||OK

--So say you have a table

CREATE TABLE MyRealTable(ColPK int identity(1,1) Primary Key, col1 int)

--You now emulate that table with an audit version

CREATE TABLE Audit_MyRealTable(ColPK int, col1 int)

--You now need an auditing trigger for INSERT, UPDATE, DELETE. I prefer 1
trigger per action.

--Here is the update trigger

CREATE TRIGGER tr_u_MyRealTable ON MyRealTable FOR UPDATE
AS
INSERT Audit_MyRealTable(ColPK, col1)
SELECT ColPK, col1 FROM UPDATED
GO

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"L Phuong" <lphuong@.neh.gov> wrote in message
news:4092936d$0$202$75868355@.news.frii.net...
> Allan, would you please show me how to do it in SQL Enterprise or in
> VB6. I'm a novice in this subject.
> Thank you.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

how to find out column name with sql server store procedure

Hi
I have table which has Fields A,B,C and D for example. is thier any way to
retrive these field column as inline table function or store procedure.
thanks
some thing like select column_name from xxxx
thanksUSE pubs
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'autho
rs'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:B4AEB01C-6652-4681-AE71-6842385E4E17@.microsoft.com...
> Hi
> I have table which has Fields A,B,C and D for example. is thier any way to
> retrive these field column as inline table function or store procedure.
> thanks
> some thing like select column_name from xxxx
> thanks
>|||Or get it all in one variable with this technique:
USE pubs
GO
DECLARE @.fields VARCHAR(1000)
SELECT @.fields = ISNULL( @.fields, '' ) + column_name + ', '
FROM INFORMATION_SCHEMA.columns
WHERE table_name = 'authors'
-- Trim trailing space and comma
SET @.fields = SUBSTRING( @.fields, 1, LEN( @.fields) -1 )
SELECT @.fields
-- sp_columns gives some useful information too.
EXEC sp_columns 'authors'
"Tibor Karaszi" wrote:

> USE pubs
> SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'aut
hors'
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "amjad" <amjad@.discussions.microsoft.com> wrote in message
> news:B4AEB01C-6652-4681-AE71-6842385E4E17@.microsoft.com...
>
>

How to find image name in URL?

If I have a string field with a URL, how can I pull out the image name?
Here are some examples and what I'd like returned:
http://somewebsite.com/myimage.gif
Return "image.gif"
http://somewebsite.com
Return "somewebsite.com"
http://somewebsite.com/89sdmksdfds9...kkkl.sldkfj.gif
Return "89sdmksdfds990s0s0s0kkkl.sldkfj.gif" (notice there are two dots
here)
http://somewebsite.com/89sdmksdfds990s0s0s0kkkl
Return "89sdmksdfds990s0s0s0kkkl"
Thanks,
BrettUse the RIGHT function to extract the right most portion of a string. Use
CHARINDEX function to find the first position of '/' in the string (reversed
in your case). Use REVERSE function to reverse a string.
Using all the above, you can do:
SELECT RIGHT( @.s, CHARINDEX('/', REVERSE( @.s ) ) - 1 )
Details & syntax of all the above mentioned functions can be found in SQL
Server Books Online.
Anith|||The OP might need to make minor adjustments for cases like:
http://www.foo.com/
http://www.foo.com/foo.gif/
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OM86YE#KFHA.904@.tk2msftngp13.phx.gbl...
> Use the RIGHT function to extract the right most portion of a string. Use
> CHARINDEX function to find the first position of '/' in the string
(reversed
> in your case). Use REVERSE function to reverse a string.
> Using all the above, you can do:
> SELECT RIGHT( @.s, CHARINDEX('/', REVERSE( @.s ) ) - 1 )
> Details & syntax of all the above mentioned functions can be found in SQL
> Server Books Online.
> --
> Anith
>

How to find duplicate records by tree field?

I have a table named student, there are tree fields GradeID, ClassID, Seat
in it.
How can I fild the duplicate record by the tree fields?
Perhaps this will help.
SELECT GradeID, ClassID, Seat, count(*) as Rows
FROM Students
GROUP BY GradeID, ClassID, Seat
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 5 Oct 2006 05:45:22 +0800, "ad" <flying@.wfes.tcc.edu.tw>
wrote:

>I have a table named student, there are tree fields GradeID, ClassID, Seat
>in it.
>How can I fild the duplicate record by the tree fields?
>
|||try this
select Count(*),GradeID, ClassID, Seat
from dbo.tablename
group by GradeID, ClassID, Seat
having count(*) > 1
the count will show you how many rows are duplicated for each instance...
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:Ox9to5$5GHA.3292@.TK2MSFTNGP02.phx.gbl...
>I have a table named student, there are tree fields GradeID, ClassID, Seat
>in it.
> How can I fild the duplicate record by the tree fields?
>
|||Thanks,
The tabe's primary is PID, how can I show the duplicate rows with PID.

> select Count(*),GradeID, ClassID, Seat
> from dbo.tablename
> group by GradeID, ClassID, Seat
> having count(*) > 1
> the count will show you how many rows are duplicated for each instance...
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:Ox9to5$5GHA.3292@.TK2MSFTNGP02.phx.gbl...
>

How to find duplicate records by tree field?

I have a table named student, there are tree fields GradeID, ClassID, Seat
in it.
How can I fild the duplicate record by the tree fields?Perhaps this will help.
SELECT GradeID, ClassID, Seat, count(*) as Rows
FROM Students
GROUP BY GradeID, ClassID, Seat
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 5 Oct 2006 05:45:22 +0800, "ad" <flying@.wfes.tcc.edu.tw>
wrote:

>I have a table named student, there are tree fields GradeID, ClassID, Seat
>in it.
>How can I fild the duplicate record by the tree fields?
>|||try this
select Count(*),GradeID, ClassID, Seat
from dbo.tablename
group by GradeID, ClassID, Seat
having count(*) > 1
the count will show you how many rows are duplicated for each instance...
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:Ox9to5$5GHA.3292@.TK2MSFTNGP02.phx.gbl...
>I have a table named student, there are tree fields GradeID, ClassID, Seat
>in it.
> How can I fild the duplicate record by the tree fields?
>|||Thanks,
The tabe's primary is PID, how can I show the duplicate rows with PID.

> select Count(*),GradeID, ClassID, Seat
> from dbo.tablename
> group by GradeID, ClassID, Seat
> having count(*) > 1
> the count will show you how many rows are duplicated for each instance...
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:Ox9to5$5GHA.3292@.TK2MSFTNGP02.phx.gbl...
>