Require's Select Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Require's Select Statement

Author

Topic

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-08-15 : 16:17:23

Hi there,

No period at end of the first sentence, if there is only one sentence. But if there is more than one sentence then there could be. Following is the data

SELECT * FROM (
Select 1 as Rno,'He is going to U.S. in 2012' Def UNION ALL
Select 2,'This is A.B.C corporation.' UNION ALL
SELECT 3,'Lets go to shopping.' UNION ALL
Select 4,'I am going home' )A

I need to select all rows having periods at end. The result should be 1,2 and 3. Can you please help in writing a query for this?

Let me know if you have any question or require further clarification.

Cheers
MIK

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-15 : 16:59:23

Here are two ways:

SELECT * FROM (
Select 1 as Rno,'He is going to U.S. in 2012' Def UNION ALL
Select 2,'This is A.B.C corporation.' UNION ALL
SELECT 3,'Lets go to shopping.' UNION ALL
Select 4,'I am going home' )A 
WHERE LEFT(REVERSE(Def), 1) = '.'

SELECT * FROM (
Select 1 as Rno,'He is going to U.S. in 2012' Def UNION ALL
Select 2,'This is A.B.C corporation.' UNION ALL
SELECT 3,'Lets go to shopping.' UNION ALL
Select 4,'I am going home' )A 
WHERE Def LIKE '%.'

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-08-15 : 17:08:00

Oh I apologize. I didn't explain the issue correctly. In existing data, the column is filled up with one, two, three .. N number of sentences. So I need to extract the rows having one sentence with a period at the end.

Using the following data

SELECT * FROM (
Select 1 as Rno,'He is going to U.S. in 2012' Def UNION ALL
Select 2,'This is A.B.C corporation.' UNION ALL
SELECT 3,'Lets go to shopping.' UNION ALL
Select 4,'I am going home' UNION ALL
SELECT 5, 'This is A.B.C corporation. The head office is New York.')A

the result should be 1, 2, 3.

I would appreciate your feedback. Many thanks

Cheers
MIK

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-15 : 21:36:21

why "1" ? there isn't a period at the end ?

KH
[spoiler]Time is always against us[/spoiler]

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-08-16 : 13:01:12

Sorry KH it was a typo. Consider is out of the result set. Or add a period at the end and make it in the result set.

Cheers
MIK

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-17 : 18:28:10

How do you define a sentence?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-08-17 : 18:44:38

Thanks Lamprey,

I need to screen the data of a column called "definitions" as per following rules

1) Definitions made up of one sentence should not be having period at the end.
2) If there are multiple sentences then every sentence should have been ended with a period.

Using the above, I need to figure out all definitions that are made up of one sentence but having period at the end.

In definitions shorts terms are used like A.B.C corporation or U.S. etc. Which should not be considered as the ending period of a sentence. You can use the above sample data. Does this make sense? I would appreciate if someone could help me out on this one.

Please let me know if you need any further clarification. Many thanks!

Cheers
MIK

3magic
Starting Member

14 Posts

Posted - 2011-08-24 : 05:31:20

Thanks Lamprey. Nice information.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-08-24 : 14:21:58

Aah 3magic, good for you but I am still waiting ...

Cheers
MIK

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2011-08-24 : 16:06:13

How are u going to find whether the "." is ending the sentence or not in case of
1. After going to U.S. I met my friend.
2. I met my friend in U.S. Then we went to see another friend.

Srinika

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-08-25 : 15:34:08

I've not seen the case 2 in my data. So for now its only the 1st one. Once its clean I would impose some checks so that these cases do not happen again.

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources