book recommendations on T-SQL/ACCESS query? 4

[rbby2003]

Can experts in Microsoft ACCESS or SQL offer some book recommendations on ACCESS/T-SQL query?

I am looking for this to do some self-study.
Some examples ( to use it in SQL 2005 query analyzer or in ACCESS 2007 environment ):
How to convert 20080723 to be a date 7/23/2008 in ACCESS 2007?
How to use query to remove non-number characters so that 904-287 - 9276 or (904)287-9276 will become 9042879276?
How to filter out rows with non-number/non-letter characters from a table: 13245a98, 1625h2872, 187am@$%9, so that 187am@$%9 will be filterred out?
How to filter out rows with multiple spaces between comma and letters: (Smith, Adam), (Jenner, Tom), so that (Smith, Adam) will be filterred out ( Lastname, then comma, one space, then Firstname ) ?

Some query solutions are very welcome for the above questions.

I appreciate your input and recommendation.

 

[SoCalAccessPro]

How to convert 20080723 to be a date 7/23/2008 in ACCESS 2007?

Either a UDF (User Defined Function) for string manupulation, or the Format function if it truely is a date.

How to use query to remove non-number characters so that 904-287 - 9276 or (904)287-9276 will become 9042879276?

Again, a UDF using the InStr function

How to filter out rows with non-number/non-letter characters from a table: 13245a98, 1625h2872, 187am@$%9, so that 187am@$%9 will be filterred out?

InStr

How to filter out rows with multiple spaces between comma and letters: (Smith, Adam), (Jenner, Tom), so that (Smith, Adam) will be filterred out ( Lastname, then comma, one space, then Firstname ) ?

Check out the Trim function


Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[jrbarnett]

For SQL Server book recommendations, see faq183-3324 (this refers to SQL Server 2000, it hasn't been updated for 2005).
Its also worth looking at Books Online, which can be downloaded for your SQL Server version from the Microsoft web site.

For books about Access, the general recommendations for all round development are the Access <version> Developers Handbook, such as

http://www.amazon.co.uk/Access-2002...=sr_1_4?ie=UTF8&s=books&qid=1216928183&sr=8-4

with

http://www.amazon.co.uk/Microsoft-O...r_1_19?ie=UTF8&s=books&qid=1216928440&sr=1-19

for Access 2007.

These cover far more than just SQL. By the way, Query Analyzer is the standalone query tool for SQL up to and including 2000; for 2005 the ability to run queries is part of Management Studio.

With regards to some of your comments:
Look at IsNumeric in VBA help to find rows with non numeric data in it.

To find rows with multiple spaces after comma
field, in your query criteria:
fieldname LIKE "*, [a-z]*"

John

 

[rbby2003]

Tyrone Lumley, thank you for your input.
I am not familiar with the functions. I have some SQL admin experience, but I am new to programming.

When the table name is member, and the field name is memberid, can you help with the specific User Defined Function to filter out 187am@$%9

How to filter out rows with non-number/non-letter characters from a table: 13245a98, 1625h2872, 187am@$%9, so that 187am@$%9 will be filterred out?

 

[rbby2003]

John ( jrbarnett ), I appreciate your help on this.
I will check out some of the recommended books.
I worked on SQL2000 for some time. For SQL 2005, it is good to know that query analyzer is not there any more and Management Studio is used in SQL 2005.


Your following query criteria resolved my problem at work. Thanks a lot.
To find rows with multiple spaces after comma
field, in your query criteria:
fieldname LIKE "*, [a-z]*"

[a-z], this part in the query is very helpful from your query criteria.


John, I asked Tyrone Lumley ( SoCalAccessPro ) for help on the following issue. Can you take a look at it?
When the table name is member, and the field name is memberid, can you help with the specific User Defined Function to filter out 187am@$%9 from the following situation?

How to filter out rows with non-number/non-letter characters from a table: 13245a98, 1625h2872, 187am@$%9, so that 187am@$%9 will be filterred out?

 

[jrbarnett]

Is there a specific pattern to your data? You could do it by checking only including fields where the third character is numeric - eg

SELECT memberID
FROM member
WHERE field LIKE "??[0-9]*"

However I'm wondering if this may exclude other legitimate data as well.

John

 

[rbby2003]

John, thanks for your response.

The data is 9-character to 12-character long ( it can be 9, 10, 11, or 12 ), and there is no specific pattern to the data.

I need to filter out rows with non-number/non-letter characters. That means each digit of the 9 ( up to 12 ) characters can be a number or a letter. I need to filter out those who inlcudes other characters ( ?, *, -, spaces, & ). I need keep those numeric/alphabetic values.

I am wondering if there is a way in ACCESS to use SQL query to achieve my goal.


SELECT memberID
FROM member
WHERE field LIKE "??[0-9]*"

John, you are right, the above query will exclude some legitimate data as well.

 

[PHV]

WHERE field LIKE "*[!0-9A-Z]*"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rbby2003]

PHV, thank you.

WHERE field LIKE "*[!0-9A-Z]*"
This did the magic, and it filters out those problem records.