How to get the position of a character in a column/String

Hi, I want to substring a part from a column, and for that I need the position of a defined character within the string, for example, I want to know the position of '#':

String: TEst#test2

So I want to know the position from # in the String

Is that possible?

Quote:

Originally Posted by JPete

Hi, I want to substring a part from a column, and for that I need the position of a defined character within the string, for example, I want to know the position of '#':

String: TEst#test2

So I want to know the position from # in the String

Is that possible?

Yes, it it CHARINDEX: SELECT CHARINDEX('#',' TEst#test2')

How to get the max value of duplicate item

Hi Every Body,

I have one table it's called -Access.It contains two columns name of that is Door,Emp_Id.

example Door Emp_Id

10 1

10 2

10 3

11 4

12 5

11 1

this is the value in that table.......

I want get Which Door number maximum Access from the table.....

Thanks & Regards,

S.Sajan

It's unclear exactly what are you asking for or what are you trying to achieve. Can you share a small code snippet or give more information? how is the data stored? what do you mean by "maximum access"? etc.

|||I'll take a guess that you're trying to find the highest door number than a particular employee has access to.

To do this you need:

Code Snippet

Select max(Door) from [-Access] where Emp_id = @.Emp_id

If instead you're trying to work out which door the most employees have access to you need:

Code Snippet

Select top 1 Door, count(Door) from [-Access] group by Door order by count(Door) desc

There are probably better ways to do the latter, but I tried something similar on a pretty big table, 90,000 records, and it's pretty quick.

Sean

Edit: I tried it on Sql Server, but I think it's the same for Access.|||

hi Sean Fowler,

That is exactly correct....in that query...............Thanks..

I Have one more doubte....

Select Top 1 Door,Count(Door) from [Access] group by Door order by Count(Door)desc

Above query disply Door Number,Which is maximum access Door Number by Employee...

Suppose two Door has access same number of time ...In that time i want display the two Door Number

..this is my requirement....

thanks in advance..

Thanks & Regards,

S.Sajan

|||

Here it is,

Code Snippet

Create Table #dooraccess (

[Door] Varchar(100) ,

[Emp_Id] Varchar(100)

);

Insert Into #dooraccess Values('10','1');

Insert Into #dooraccess Values('10','2');

Insert Into #dooraccess Values('10','3');

Insert Into #dooraccess Values('11','4');

Insert Into #dooraccess Values('12','5');

Insert Into #dooraccess Values('11','1');

On SQL Server 2000/2005,

Code Snippet

Select Main.* from

(SelectDoor, Count([Emp_Id]) [AccessCount] from #dooraccess Group By Door) as Main

Join

(

Select Max(AccessCount) AccessCount from

(

SelectDoor, Count([Emp_Id]) [AccessCount] from #dooraccess Group By Door

) as Data

) as MaxAccess

On Main.AccessCount = MaxAccess.AccessCount

--or

Code Snippet

Select Top 1 Door, Count([Emp_Id]) [AccessCount] from #dooraccess Group By Door Order By [AccessCount] Desc

On SQL Server 2005,

Code Snippet

;With CTE

as

(

Select Door, Count([Emp_Id]) Over(partition By Door) [AccessCount] from #dooraccess

)

, CTE2

as

(

Select * , Max([AccessCount]) Over() MaxAccessCount from CTE

)

Select * from CTE2 Where AccessCount = MaxAccessCount

--or

Code Snippet

Select Top 1 Door, Count([Emp_Id]) Over(partition By Door) [AccessCount] from #dooraccess order By [AccessCount] Desc

|||All good suggestions. You can also do this:

Code Snippet

Select Door, count(Door)
from [#dooraccess]
group by Door
having count(Door) = (select top 1 count(Door) from #dooraccess group by Door order by count(Door) desc)

Seansql

how to get the last record from the database?

hi friends,

i tried to get the last record in a specified column from a database. but it fails.

for example, i have a column name as "empid" in the table name "detail". in that, the records are in the form of "Emp1, Emp2, Emp3, .........Emp12."

i used this query --> select top 1 empid from detail order by empid desc

it is showing only Emp9.

it should show Emp12.

but the id's are showing in ascending order(Emp1,2,3,.....12) when i used this query --> select empid from detail

i've a gridview to show all Employee id's with paging(1-10 per page). So Emp12 would be showed in the second page. how to get that value "Emp12" from that gridview?

but i need a SQL query rather than this method (getting from gridview).

its urgent. Plz...................

since you are sorting a text column, you will not get a proper numerical sort. the correct text sort sequence would be

Emp1
Emp11
Emp2
Emp21
...
Emp9

in order to approach a numerical sort with text data, you need to store leading zeros with the numeric portion of your text

Emp000001
Emp000002
Emp000009
Emp000011
Emp000021

I would recommend that your seperate out the numerical data though and store it in a column with a numeric datatype.

|||

Uuh!.......... i stored Zeroes........no use............still it is showing "Emp09".............

suggest me another method............

|||

Although frankly speaking I didn't like this approach of storing string int mixed values and then trying it to sort it on the int part only, I thought to create at least something to get out of this kind of problem. I eventually came up with creating a UDF. The UDF code and sample code to test the function is as below. I hope you'll like it, but my advice is to separate out the string and int part, this will help you in days to come. Anyways, below is the code.

--- My functioncreate function ParseInt(@.valuevarchar(8000))returnsbigintasbegindeclare @.char varchar(1)declare @.lengthintdeclare @.counterintdeclare @.lastIndexintdeclare @.varIntvarchar(100)set @.varInt =''set @.counter = 1set @.length =len ( @.value )set @.lastIndex = 0while @.counter <= @.lengthbeginif ( ( @.lastIndex = 0or @.lastIndex + 1 = @.counter )and ( ascii (substring ( @.value , @.counter , 1 ) )between 48and 57 ) )beginset @.varInt = @.varInt +substring ( @.value , @.counter , 1 )set @.lastIndex = @.counterendset @.counter = @.counter + 1endreturncast ( @.varIntas bigint )endGO--- Sample Data to test the function

create table tbl(empvarchar(50))declare @.iintset @.i = 1while @.i <= 30begininsert tbl ( emp )select'empid' +cast ( @.ias varchar)set @.i = @.i + 1endGOselect * , dbo.ParseInt ( emp )from tblorder by dbo.ParseInt ( emp )desc

Hope this will help.

|||

Hi,

To make things simple, just add an identity column (eg: ID) to your table Detail and then fire the query

select top 1 * from Detail order by ID desc

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Hi,

In case you are want to know how to add an identity column to an existing table :

alter table Detail
add ID int IDENTITY(1,1) not null

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Hey friends,

no one works which is provided by you.................anyway i found a solution (not for sorting)........... thank u friends, for ur all replies............

How to get String representation of a Hex number?

Hi,
I want to get the string representation of a hex number from a
varBinary column of a table.
For example I want to get the output : 'The Hex value is 0xFF'
but
select 'The Hex value is ' + convert(varchar(10), 0xFF)
retruns the ascii charecter for 0xFF

Any idea how do I get the hex form as it is?
thanks.
Supratim[posted and mailed, please reply in news]

Supratim (supratim@.sagemetrics.com) writes:
> I want to get the string representation of a hex number from a
> varBinary column of a table.
> For example I want to get the output : 'The Hex value is 0xFF'
> but
> select 'The Hex value is ' + convert(varchar(10), 0xFF)
> retruns the ascii charecter for 0xFF
> Any idea how do I get the hex form as it is?

You can use

select master.dbo.fn_varbintohexstr(@.binvalue)

Note, however, that fn_varbintohexstr is un documented function, and thus
unsupported.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That works exactly the way I want ..
Thanks a lot
-Supratim

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9558844747DDEYazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Supratim (supratim@.sagemetrics.com) writes:
> > I want to get the string representation of a hex number from a
> > varBinary column of a table.
> > For example I want to get the output : 'The Hex value is 0xFF'
> > but
> > select 'The Hex value is ' + convert(varchar(10), 0xFF)
> > retruns the ascii charecter for 0xFF
> > Any idea how do I get the hex form as it is?
> You can use
> select master.dbo.fn_varbintohexstr(@.binvalue)
> Note, however, that fn_varbintohexstr is un documented function, and thus
> unsupported.

How to get records (In this week) only?

Hi,

How to get records from MSSQL database which only recorded from the biging of this week?

in example my week begin from Sunday..

You might want to be more specific. Is this for a single table or for all tables? Does/do your table/s have timestamp columns?|||

Only for one table, and yes the table have timestamp column.

I'm also trying to use this query to get the number of records inserted on this month:

SELECT COUNT(*)

FROM MyTable

WHERE DateTimeColumn > MONTH(1)

It doesnt return the correct value for records inserted on Feb!!

|||

You're comparing against a constant - MONTH(1)

Try this instead:

WHERE MONTH(DateTimeColumn) > 1

Note, though, that this may not be optimal on large tables, since the function wrapped around DateTimeColumn will likely prevent an index to be used, if there is any on that column..

/Kenneth

|||

Thanks KeWin, this format is working perfectly..

Whats the optimal solution for Larg Tables?

And what about (In this week)? how can I get it?

|||Hi,

SELECT*FROM

SomeTable

WHEREDATENAME(wk,SomeDateColumn)=DATENAME(wk,GETDATE())AND

YEAR(SomeDateColumn)=YEAR(GETDATE())

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens K. Suessmeyer,

it worked :)

|||

Weeknumbers are probably the trickiest part of a date.

While this works well for US-style weeknumbers, just be aware that it doesn't work as well for those of us who use the ISO week numbering scheme. That is, weeknumbers are dependant on which country's calendar it's applyed against.

/Kenneth

How to get records (In this week) only?

Hi,

How to get records from MSSQL database which only recorded from the biging of this week?

in example my week begin from Sunday..

You might want to be more specific. Is this for a single table or for all tables? Does/do your table/s have timestamp columns?|||

Only for one table, and yes the table have timestamp column.

I'm also trying to use this query to get the number of records inserted on this month:

SELECT COUNT(*)

FROM MyTable

WHERE DateTimeColumn > MONTH(1)

It doesnt return the correct value for records inserted on Feb!!

|||

You're comparing against a constant - MONTH(1)

Try this instead:

WHERE MONTH(DateTimeColumn) > 1

Note, though, that this may not be optimal on large tables, since the function wrapped around DateTimeColumn will likely prevent an index to be used, if there is any on that column..

/Kenneth

|||

Thanks KeWin, this format is working perfectly..

Whats the optimal solution for Larg Tables?

And what about (In this week)? how can I get it?

|||Hi,

SELECT*FROM

SomeTable

WHEREDATENAME(wk,SomeDateColumn)=DATENAME(wk,GETDATE())AND

YEAR(SomeDateColumn)=YEAR(GETDATE())

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens K. Suessmeyer,

it worked :)

|||

Weeknumbers are probably the trickiest part of a date.

While this works well for US-style weeknumbers, just be aware that it doesn't work as well for those of us who use the ISO week numbering scheme. That is, weeknumbers are dependant on which country's calendar it's applyed against.

/Kenneth

How to get records (In this week) only?

Hi,

How to get records from MSSQL database which only recorded from the biging of this week?

in example my week begin from Sunday..

You might want to be more specific. Is this for a single table or for all tables? Does/do your table/s have timestamp columns?|||

Only for one table, and yes the table have timestamp column.

I'm also trying to use this query to get the number of records inserted on this month:

SELECT COUNT(*)

FROM MyTable

WHERE DateTimeColumn > MONTH(1)

It doesnt return the correct value for records inserted on Feb!!

|||

You're comparing against a constant - MONTH(1)

Try this instead:

WHERE MONTH(DateTimeColumn) > 1

Note, though, that this may not be optimal on large tables, since the function wrapped around DateTimeColumn will likely prevent an index to be used, if there is any on that column..

/Kenneth

|||

Thanks KeWin, this format is working perfectly..

Whats the optimal solution for Larg Tables?

And what about (In this week)? how can I get it?

|||Hi,

SELECT * FROM

SomeTable

WHERE DATENAME(wk,SomeDateColumn) = DATENAME(wk,GETDATE()) AND

YEAR(SomeDateColumn) = YEAR(GETDATE())

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens K. Suessmeyer,

it worked :)

|||

Weeknumbers are probably the trickiest part of a date.

While this works well for US-style weeknumbers, just be aware that it doesn't work as well for those of us who use the ISO week numbering scheme. That is, weeknumbers are dependant on which country's calendar it's applyed against.

/Kenneth

How to get node name in SQL 2005 nodes query? thanks

for example,xml like

'<name>test</name><age>10</age>'

select ? from @.xml.nodes('/*')

how to get the node tag name like name and age?

thanks

You can try the following code:

 XmlDocument doc =new XmlDocument(); doc.Load("yourpath.xml");//Select and display the value of all the ISBN attributes. XmlElement root = doc.DocumentElement;if (root.HasChildNodes) {for (int i = 0; i < root.ChildNodes.Count; i++) { Response.Write(root.ChildNodes[i].Name); } }

Hope it helps.|||

Sorry. I didn't make it clear. What I want to know is how to get nodename in the SQL 2005 XML query.

Thanks anyway.

|||

Hi

I triedSELECT [column1].query('data(/root/*)') FROM [xml_test] it returned the data value of the nodes.

But when I triedSELECT [column1].query('node-name(/root/*)') FROM [xml_test]
It produced an error said node-name which was defined in xpath was not supported.

Hope others have a good idea.

How to get identity from parent Table and insert it into the child

Leaving aside for the moment your table schema, which seems a bit odd, This
is an example of where using Surrogate keys can be a bit trickier than using
meaningful keys... In order to do this is robust way, you must have some
mechanism (Other than the Surogate Identity Key) to "connect" the child
records to the right parent record.
Now to the table schema... regarding Your "Child" table, the one you have
named TBCOURSE, from the name, it would seem to contain Course data... But i
f
that's true, it makes no sense to have a FK in it that points to one row in
a
table which contains Students.. That implies that for each course, there is
but one and only one student, or some single student that plays some special
distinct role related to that course... SO, since that makes no sense, the
next logical question is
Exactly what do the rows in these tables represent? nd then...
What exactly are you trying to do with this insert ?
"RayAll" wrote:

> I have a table with the following schema
> F_NAME L_NAME COURSE_ID COURSE_DESC
> this table contains 10 records ,and I need to insert these records into 2
> tables .TBSTUDENT and TBCOURSE.
> in TBSTUDENT ,I have student_id which is an identity column and in TBCOURS
E
> ,I have a foriegn key to this field. How can I isert 10 records from the
> first table into TBSTUDENT AND TBCOURSE ,where I should first get the
> identity generated from the first insert and use it in the second
> insert(into TBCOURSE).
> Thanks
>
>Thanks for your reply.
No,The schema of the table is not odd ,because this is a buffer table which
I amusing in a DTS package to read records from a CSV file into this table
(and do the validation during transformation) and then from here I have to
distribute the records into the relavant tables.Dose it still seem to be
odd?
I made the chema myself ,because I wanted to simplify things here are the
schema of the 3 other tables I am trying to insert into.
**************CLIENT:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[PE_CLI_FK]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[PROGRAM_ENROLLMENT] DROP CONSTRAINT PE_CLI_FK
GO
CREATE TABLE [dbo].[CLIENT] (
[INTERNAL_ID] [numeric](12, 0) IDENTITY (1, 1) NOT NULL ,
<--this is the identity field
[CLIENT_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USERNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
.....
) ON [PRIMARY]
GO
***********PROGRAM_ENROLLMENT:
CREATE TABLE [dbo].[PROGRAM_ENROLLMENT] (
[PROGRAM_ENROLLMENT_ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
[INTERNAL_ID] [numeric](12, 0) NOT NULL , <--This is
foriegn key to the Client Table
...
) ON [PRIMARY]
GO
***********PERSONAL_INFO:
CREATE TABLE [dbo].[PERSONAL_INFO] (
[FIRST_NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[MIDDLE_NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
...
[INTERNAL_ID] [numeric](12, 0) NOT NULL , <--this is also a foriegn
key to Client
...
[Timestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO
******************
Here is what I'm trying to do:
I am trying to get each record from my buffer table and insert it into these
three tables,but before inserting into the Programe_enrolment and
Personal_info ,I have to have the InternalID of the Client Table,then I
should use the internalID in every other table I insert the rest of the
record.
For instance let's say this is my record in buffer table:
FirstName clientID UserName
Ray 1233 ray5531 ............
First I insert this into client and I get the internalID of for instance
1000 ,then I need to use this and insert other fields of this record into
Personal_Info and PROGRAM_ENROLLMENT.
Same process for all the other records in the buffer table.
Thanks for your time.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:A31711F6-1A30-4DDC-9741-5EF81D96CE62@.microsoft.com...
> Leaving aside for the moment your table schema, which seems a bit odd,
> This
> is an example of where using Surrogate keys can be a bit trickier than
> using
> meaningful keys... In order to do this is robust way, you must have some
> mechanism (Other than the Surogate Identity Key) to "connect" the child
> records to the right parent record.
> Now to the table schema... regarding Your "Child" table, the one you have
> named TBCOURSE, from the name, it would seem to contain Course data... But
> if
> that's true, it makes no sense to have a FK in it that points to one row
> in a
> table which contains Students.. That implies that for each course, there
> is
> but one and only one student, or some single student that plays some
> special
> distinct role related to that course... SO, since that makes no sense,
> the
> next logical question is
> Exactly what do the rows in these tables represent? nd then...
> What exactly are you trying to do with this insert ?
> "RayAll" wrote:
>|||Your table design is incomplete because you haven't declared natural keys on
these tables. Declare the proper keys and it's easy to do what you require.
See this example:
http://www.google.co.uk/groups?selm...%40giganews.com
On the other hand, replacing one surrogate key with another seems
particularly pointless anyway.
David Portas
SQL Server MVP
--|||Thanks for your reply.
Where am I replacing surrogate key with another one.I have only one
surrogate key in client and I am using this in other tables to maintanin a
link between client and other informations.What's wrong with this?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:0t-dner_57mHRdTfRVn-vg@.giganews.com...
> Your table design is incomplete because you haven't declared natural keys
> on these tables. Declare the proper keys and it's easy to do what you
> require. See this example:
> http://www.google.co.uk/groups?selm...%40giganews.com
> On the other hand, replacing one surrogate key with another seems
> particularly pointless anyway.
> --
> David Portas
> SQL Server MVP
> --
>|||What's wrong is that IDENTITY should never be the *only* key of a table. By
definition IDENTITY should always be redundant and breaking that rule
destorys integrity. For example, you don't want duplicate clients with the
*same* Client_ID and Username. Declare the keys on the other columns and
your problem is solved. Take a look at the example I posted.
David Portas
SQL Server MVP
--|||Symantically ,there is a compund key there ,which I can rely on.However It
hasn't been created,but technically there is no record with same "ClientID"
and "Affiliate_ID".
Yes,your answer was really helpfu.I think that's the way to do so.
Thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OZadncsqxMs9QNTfRVn-jw@.giganews.com...
> What's wrong is that IDENTITY should never be the *only* key of a table.
> By definition IDENTITY should always be redundant and breaking that rule
> destorys integrity. For example, you don't want duplicate clients with the
> *same* Client_ID and Username. Declare the keys on the other columns and
> your problem is solved. Take a look at the example I posted.
> --
> David Portas
> SQL Server MVP
> --
>

how to get error message

Hello!

I am using T-SQL (SQL Server 2000). I would like to get error message

(for example stored in some string variable) when error appeares. I

found only system variable @.@.error which holds the error number of the

specific error. But I didn't find any way to get a message which is

written in SQL Qery analyzer in the Message window beneath the query

window.

Is it possible to get that message somehow? I would like to store it in my error table...

Thanks a lot,

Ziga

Hi

You can query the sysmessages table in the Master database for the error number that you receive, this will give you the description of the error. Books Online contains a description of the sysmessages table schema should you want to know what each field is, look under sysmessages.

HTH

|||Thanks a lot!

But anyway I would like to get the same message as is reported in sql

server (with object names - attribute name). Systables contains

attribute description with description of the messages with

placeholders. I don't know how to put some real object names on the

place of placeholders... Actually I can't put them, because sql server

should somehow (cause I don't know on which attribute it breaks).|||

Hi

I don't believe there is a way of finding out the exact same message as displayed by SQL Server when an error occurs (although I could be wrong). If your procedure does multiple things then you could use the @.@.Error to determine if an error has occurred at each step of the procedure and then you would have at least more knowledge of where the error occurred. Add to that, the error number returned, you should then be able to log sufficient error information.

HTH

|||Unfortunately, there is no way to obtain the system error message in TSQL. SQL Server 2005 has new exception handling features that provide you this capability. So best is to log server generated error messages on the client-side and capture only the error numbers on the server.