SQL query needed for locating specific text criteria

Question

Post reply

SQL query needed for locating specific text criteria

NadJ

Ten Centuries

Points: 1031
More actions
October 26, 2012 at 8:20 am

#266128

Hi all,
Been a long time since I did anything with SQL and would like your help on the following requirement:
I need a SQL query (T-SQL 2000 friendly) that will return all the rows in my table that contain columns (9 of them) containing words:
1.) That BEGIN with an upper case, e.g. Car, Plane, Train
2.) AND not including words that are entirely in uppercase, e.g. CAR, PLANE, TRAIN
3.) AND not including words that match criteria 1. that are the first words in the column
3.) If 1. and 2. exist, or 1. and 3. exist also return the row.
I have semi pro experience with T-SQL but it goes back over 6 or 7 years ago!
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Can you post ddl and some sample data? Take a look at the first link in my signature for help in what to post.

This is a rather strange requirement but it is certainly feasible.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

NadJ Ten Centuries Points: 1031 More actions · Answer 2

Hi,

I should admit, at this time I don't even have the database or tables created! The data is in Excel and I was thinking of doing a straight forward tab delimited text file import as Excel isn't great for data cleansing. The data can all be treated as text as no date/time or number calculations will be required.

It's a simple Question and Answers spreadsheet, multiple choice. The data is poorly done with random caps here and there. I know you don't like the below format but as it's still in Excel, I'll paste an example of what I'd consider to be a row which gets displayed as a result of this query.

No.

302

Category

0

Question

Avionics ventilation system indications may be found on which ECAM page(s)?

Answer1

Only the bleed page.

Answer2

Only the CAB press page.

Answer3

The in-flight ECAM cruise page.

Answer4

The in-flight ECAM cruise page and the CAB press pages.

Answer5

(blank)

CorrectAnswer

B

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

OK now you have me confused....at first you said something about there being 9 columns and you wanted a select statement. Now it seems that there is only 1 column and you want an update statement? It seems you have a question but at this point you aren't quite sure what the question is. Maybe once you bring into a table you can post your ddl and sample data along with the desired outcome. There is nothing I can do to help you at this point because your problem is not defined.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

NadJ Ten Centuries Points: 1031 More actions · Answer 4

Oh poo! Sorry, I've not done anyone any favors by posting in the way I have. I can confirm there are 9 columns, the example data below is per column (I just laid it out stupidly), e.g.

No., Category, Question, Answer1, Answer2, Answer3, Answer4, Answer5, CorrectAnswer and I can confirm I need a select statement not UPDATE.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

NadJ (10/26/2012)
Oh poo! Sorry, I've not done anyone any favors by posting in the way I have. I can confirm there are 9 columns, the example data below is per column (I just laid it out stupidly), e.g.
No., Category, Question, Answer1, Answer2, Answer3, Answer4, Answer5, CorrectAnswer and I can confirm I need a select statement not UPDATE.

OK so post the ddl, sample data and desired output.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 6

an example of what we need to try and help you:

--REQUIRED DDL

--DROP TABLE [dbo].[SAMPLETABLE]

CREATE TABLE [dbo].[SAMPLETABLE] (

[NO] INT NULL,

[CATEGORY] INT NULL,

[QUESTION] VARCHAR(100) NULL,

[ANSWER1] VARCHAR(100) NULL,

[ANSWER2] VARCHAR(100) NULL,

[ANSWER3] VARCHAR(100) NULL,

[ANSWER4] VARCHAR(100) NULL,

[ANSWER5] VARCHAR(100) NULL,

[CORRECTANSWER] VARCHAR(100) NULL)

--REQUIRED SAMPLE DATA

INSERT INTO SAMPLETABLE

SELECT

302,

0,

'Avionics ventilation System indications may be Found on which ECAM Page(s)?',

'Only the bleed Page.',

'Only the CAB press page.',

'The in-flight ECAM cruise page.',

'The in-flight ECAM Cruise page and the cab press pages.',

'(blank)',

'B'

SELECT * FROM SAMPLETABLE

WHERE ???

--1.) That BEGIN with an upper case, e.g. Car, Plane, Train

--2.) AND not including words that are entirely in uppercase, e.g. CAR, PLANE, TRAIN

--3.) AND not including words that match criteria 1. that are the first words in the column

--3.) If 1. and 2. exist, or 1. and 3. exist also return the row.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!