Newbie question on SQL Query writing

Hi,

I am new to writing SQL queries in MS SQL & would like to do the
following: Write a query to retrieve all strings that start with a
particular value.

Basically, I am looking for the SQL equivalent of the regex "^".

Thanks,
AshokSELECT column1 FROM mytable WHERE charindex('Search Text', column1) = 1|||On 18 May 2005 08:01:46 -0700, ashok.anbalan@.gmail.com wrote:

>Hi,
>I am new to writing SQL queries in MS SQL & would like to do the
>following: Write a query to retrieve all strings that start with a
>particular value.
>Basically, I am looking for the SQL equivalent of the regex "^".
>Thanks,
>Ashok

Hi Ashok,

Assuming they need to start with 'a':

SELECT Column list
FROM MyTable
WHERE TheStringColumn LIKE 'a%'

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Newbie Needs Help Getting Date

Hello,
I have a field in a table that automatically saves the Date and Time.
My field looks like this: 10/10/2003 2:24:40 PM
I need to retrieve ONLY the date portion of this field. Is there a simple way to do this? Any help is greatly appreciated!
Thanks!SELECT CONVERT(varchar(26),GetDate(),1)

Look up CONVERT in BOL for more options...|||...but you'll need to cast is as a date value again if you want to use it like a date:

SELECT CAST(CONVERT(varchar(26),GetDate(),1) as DateTime)

...or my preference because it sorts correctly as varchar...
SELECT CAST(CONVERT(varchar(10),GetDate(),120) as DateTime)

Everybody has their own preference for this, but the general method of casting as a string and then back to datetime (if necessary) is standard.

blindman

P.S.: If you still need help getting a date, check out this link:
http://personals.yahoo.com/|||heh, nice P.S. -- this forum needs the occasional chuckle

by the way, style 120, which blindman mentioned, is the ISO standard format yyyy-mm-dd

is sorts nicely as a varchar because it goes from highest to lowest (well, i didn't say that right, but i hope you know what it means)

in addition, ISO format is always correctly interpreted by all databases when inserting dates

i cannot count how many times i've seen posts on various forums from people who have run into trouble trying to insert values like '04/03/2003' (march 4th or april 3rd?)|||Originally posted by r937
i cannot count how many times i've seen posts on various forums from people who have run into trouble trying to insert values like '04/03/2003' (march 4th or april 3rd?)

Funny, I'm trying to explain that to my fellow programmers right now ... They've got the most intricate functions to fix those problems and they all screw up when someone changes the regional settings on their servers :)|||Nice PS: However, my wife MAY be mad at me for looking at personals, or maybe she is tired of me and would like that!!! LOL

I may not have described my problem well enough or probably I am not understanding since I am new to all this. This is more of what I need...

I have these dates and times: Field Name is GetDate

10/10/2003 2:24:40 PM
10/10/2003 3:34:41 PM
10/20/2003 2:24:40 PM
10/20/2003 6:54:20 PM
10/21/2003 2:24:40 PM
10/21/2003 8:34:43 PM

I need to pull out the different dates and display only one date:

10/10/2003
10/20/2003
10/21/2003

Does that make sense??
Thanks again!!!|||Please tell me you didn't name your field "GetDate". :confused:

You actually are performing two operations, the first is to truncate datetime values (which we've shown you how to do) and the second is reducing the result set to a single row for each date. You can do this using either the SELECT DISTINCT syntax, or if you need to perform aggregate functions on other columns (sum, count, avg etc...) you can use the GROUP BY syntax.

Read up on the SELECT statement in Books Online. It is very powerful, has many options, and is 90%+ of the statements DBA's write.

blindman|||Thanks for the assistance...

I didn't name the field getDate: I misstyped... it is DateSubmitted...

Thinking dateSubmitted and getDate came out of fingers... hehehe

Thanks so much for everything!!!!

newbie - Display rows with identical values

Hello,
how can i retrieve duplicate records?
I have this table with about 6500 records, and i know that there are a few
where a combination (Column1 - Column2) is identical
how can i retrieve these?SELECT Column1, Column2
FROM YourTable
GROUP BY Column1 - Column2
HAVING COUNT(*) > 1
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"benoit" <benoit@.discussions.microsoft.com> wrote in message
news:2851EFFB-2722-47E6-A654-D953AA4FB86A@.microsoft.com...
> Hello,
> how can i retrieve duplicate records?
> I have this table with about 6500 records, and i know that there are a few
> where a combination (Column1 - Column2) is identical
> how can i retrieve these?
>
>|||hi benoit,
Just a question, your table own a indentity field?
If so, use this:
delete from table1
where <identityfield> not in
(select max(<identityfiedl> ) from table1
group by [<field1>,<field2>]
Otherwise, let me know or post DDL
regards,
"benoit" wrote:

> Hello,
> how can i retrieve duplicate records?
> I have this table with about 6500 records, and i know that there are a few
> where a combination (Column1 - Column2) is identical
> how can i retrieve these?
>
>|||i was thinking the same way as roji
use northwind
select lastname,firstname into x from employees
union all
select top 5 lastname,firstname from employees
go
select lastname, firstname from x
group by lastname,firstname
having count(*)>1
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"benoit" wrote:

> Hello,
> how can i retrieve duplicate records?
> I have this table with about 6500 records, and i know that there are a few
> where a combination (Column1 - Column2) is identical
> how can i retrieve these?
>
>|||works great !
thx
"Roji. P. Thomas" wrote:

> SELECT Column1, Column2
> FROM YourTable
> GROUP BY Column1 - Column2
> HAVING COUNT(*) > 1
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "benoit" <benoit@.discussions.microsoft.com> wrote in message
> news:2851EFFB-2722-47E6-A654-D953AA4FB86A@.microsoft.com...
>
>|||Hi
This article had written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"benoit" <benoit@.discussions.microsoft.com> wrote in message
news:2851EFFB-2722-47E6-A654-D953AA4FB86A@.microsoft.com...
> Hello,
> how can i retrieve duplicate records?
> I have this table with about 6500 records, and i know that there are a few
> where a combination (Column1 - Column2) is identical
> how can i retrieve these?
>
>

Newb question

I am attempting to retrieve specific task related data from a Project server
environment for input to an Excel 2003 spreadsheet and have created the
following SQL view using:
SELECT dbo.MSP_PROJECTS.PROJ_NAME, dbo.MSP_TASKS.TASK_NAME,
dbo.MSP_TASKS.TASK_IS_MILESTONE
FROM dbo.MSP_PROJECTS CROSS JOIN
dbo.MSP_TASKS
WHERE (dbo.MSP_PROJECTS.PROJ_ID = 92) AND
(dbo.MSP_TASKS.TASK_IS_MILESTONE = 1)
My logic here is that this query will pull back all tasks that are
milestones from 'project 92' only. However, this query pulls back all
milestones from all projects... I have no idea why, could someone please
shed some light on this newb.
Many thanks,
Nock (SQL Newb, Australia)I think the CROSS JOIN might be a clue. I would just use a JOIN
"Nock" wrote:

> I am attempting to retrieve specific task related data from a Project serv
er
> environment for input to an Excel 2003 spreadsheet and have created the
> following SQL view using:
> SELECT dbo.MSP_PROJECTS.PROJ_NAME, dbo.MSP_TASKS.TASK_NAME,
> dbo.MSP_TASKS.TASK_IS_MILESTONE
> FROM dbo.MSP_PROJECTS CROSS JOIN
> dbo.MSP_TASKS
> WHERE (dbo.MSP_PROJECTS.PROJ_ID = 92) AND
> (dbo.MSP_TASKS.TASK_IS_MILESTONE = 1)
> My logic here is that this query will pull back all tasks that are
> milestones from 'project 92' only. However, this query pulls back all
> milestones from all projects... I have no idea why, could someone please
> shed some light on this newb.
> Many thanks,
> Nock (SQL Newb, Australia)|||it is because of the CROSS JOIN used to connect the MSP_PROJECTS and
MSP_TASKS tables. A cross join between two tables produces what is known as
a
Cartesian product, which is a table that contains all of the possible
combinations between the rows between the input tables. In other words, a
cross join between two tables X and Y with x and y rows respectively will
contain 1 row for each y rows for each row in X, for a total of x times y
rows.
What you probably want is something like this
SELECT dbo.MSP_PROJECTS.PROJ_NAME, dbo.MSP_TASKS.TASK_NAME,
dbo.MSP_TASKS.TASK_IS_MILESTONE
FROM dbo.MSP_PROJECTS INNER JOIN
dbo.MSP_TASKS ON dbo.MSP_PROJECTS.PROJ_ID =
dbo.MSP_TASKS.PROJ_ID
WHERE (dbo.MSP_PROJECTS.PROJ_ID = 92) AND
(dbo.MSP_TASKS.TASK_IS_MILESTONE = 1)
"Nock" wrote:

> I am attempting to retrieve specific task related data from a Project serv
er
> environment for input to an Excel 2003 spreadsheet and have created the
> following SQL view using:
> SELECT dbo.MSP_PROJECTS.PROJ_NAME, dbo.MSP_TASKS.TASK_NAME,
> dbo.MSP_TASKS.TASK_IS_MILESTONE
> FROM dbo.MSP_PROJECTS CROSS JOIN
> dbo.MSP_TASKS
> WHERE (dbo.MSP_PROJECTS.PROJ_ID = 92) AND
> (dbo.MSP_TASKS.TASK_IS_MILESTONE = 1)
> My logic here is that this query will pull back all tasks that are
> milestones from 'project 92' only. However, this query pulls back all
> milestones from all projects... I have no idea why, could someone please
> shed some light on this newb.
> Many thanks,
> Nock (SQL Newb, Australia)|||Nock,
What is the common column between the tables? n other words, does MSP_TASKS
have a foreign Key column to the Primary Key column on MSP_PROJECTS, or
vice-versa.
Whichever is the key column you should then use a standard JOIN statement.
If you want just the Tasks that are associated with Project 92, then you
should use the following...
SELECT proj.PROJ_NAME,
task.TASK_NAME,
task.TASK_IS_MILESTONE
FROM dbo.MSP_PROJECTS proj LEFT JOIN
dbo.MSP_TASKS task ON proj.{PK} = task.{FK}
WHERE (proj.PROJ_ID = 92)
AND (task.TASK_IS_MILESTONE = 1)
The LEFT Join will ensure you get all Project Data back together with any
Task data that is relevent, or NULL values if none present. If you use INNER
join then there will need to be at least one reacord in each table.
Enjoy,
"Nock" wrote:

> I am attempting to retrieve specific task related data from a Project serv
er
> environment for input to an Excel 2003 spreadsheet and have created the
> following SQL view using:
> SELECT dbo.MSP_PROJECTS.PROJ_NAME, dbo.MSP_TASKS.TASK_NAME,
> dbo.MSP_TASKS.TASK_IS_MILESTONE
> FROM dbo.MSP_PROJECTS CROSS JOIN
> dbo.MSP_TASKS
> WHERE (dbo.MSP_PROJECTS.PROJ_ID = 92) AND
> (dbo.MSP_TASKS.TASK_IS_MILESTONE = 1)
> My logic here is that this query will pull back all tasks that are
> milestones from 'project 92' only. However, this query pulls back all
> milestones from all projects... I have no idea why, could someone please
> shed some light on this newb.
> Many thanks,
> Nock (SQL Newb, Australia)|||Thanks Mark et al, much appreciated and great explanation.
I'm in one of those situations where I've been asked to become a 'SQL
person' in a day...
Loving life :)
Cheers,
Nock
"Mark Williams" wrote:
> it is because of the CROSS JOIN used to connect the MSP_PROJECTS and
> MSP_TASKS tables. A cross join between two tables produces what is known a
s a
> Cartesian product, which is a table that contains all of the possible
> combinations between the rows between the input tables. In other words, a
> cross join between two tables X and Y with x and y rows respectively will
> contain 1 row for each y rows for each row in X, for a total of x times y
> rows.
> What you probably want is something like this
> SELECT dbo.MSP_PROJECTS.PROJ_NAME, dbo.MSP_TASKS.TASK_NAME,
> dbo.MSP_TASKS.TASK_IS_MILESTONE
> FROM dbo.MSP_PROJECTS INNER JOIN
> dbo.MSP_TASKS ON dbo.MSP_PROJECTS.PROJ_ID =
> dbo.MSP_TASKS.PROJ_ID
> WHERE (dbo.MSP_PROJECTS.PROJ_ID = 92) AND
> (dbo.MSP_TASKS.TASK_IS_MILESTONE = 1)
> --
> "Nock" wrote:
>|||Try these sites for a general SQL overview...
http://www.w3schools.com/sql/sql_intro.asp
http://sqlzoo.net/
"Nock" <Nock@.discussions.microsoft.com> wrote in message
news:05979A9F-A3BC-401B-867F-CDC7114D2CE1@.microsoft.com...
> Thanks Mark et al, much appreciated and great explanation.
> I'm in one of those situations where I've been asked to become a 'SQL
> person' in a day...
> Loving life :)
> Cheers,
> Nock
> "Mark Williams" wrote:
>
as a
a
will
y
server
the
please