Need to find a character - RUSTY SOLVED IT

Get help on databases - MySQL, Oracle, Access, etc.
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Need to find a character - RUSTY SOLVED IT

Post by Tribble »

Hi,

I have a field with text strings in of 19 characters each. I need to see if there is a "G" in the string - if there is - the broker will need to write an exam. The G can be any or all of the characters in the string. There are combinations of N, G and S.

I need to do this in a query. There is no find function and I am at a loss.

Please help - this is urgent!
Last edited by Tribble on 04 Feb 2009, 11:38, edited 1 time in total.
Image
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: Need to find a character in a string - MS Access

Post by rustypup »

instr()?

returns >0 if the substring is present
returns 0 if not..
Most people would sooner die than think; in fact, they do so - Bertrand Russel
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Need to find a character in a string - MS Access

Post by Tribble »

You are my saviour!!!!!!!!!!!!!!!!!!!!!!!!! You are amazing and wonderful and awesome and spectacular and just THE cleverest person on this entire forum!!!!!!!!!!!!!!
:love5: :love7:

:yay:
Image
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Need to find a character in a string - MS Access

Post by Tribble »

Thank you so much. It is working beautifully.

Can someone lock the thread now please? :mrgreen:
Image
D3PART3D
Starbound's Dear
Posts: 16294
Joined: 01 Dec 2004, 02:00
Contact:

Re: Need to find a character in a string - MS Access

Post by D3PART3D »

Tribble wrote:Can someone lock the thread now please? :mrgreen:
No. We must now spam it to death.

The rusty solvents demand it!
Ceterum autem censeo Samsung Mobile esse delendam.

When something is important enough, you do it even if the odds are not in your favor.
- Elon Musk
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: Need to find a character - RUSTY SOLVED IT

Post by RuadRauFlessa »

What language is it in Tribble. Just as a reference though.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Need to find a character - RUSTY SOLVED IT

Post by Tribble »

It was MS Access - just in a plain query. In Excel I use the find function and was thrown because Access doesn't have it. I never knew about InStr() :D Now I do.
Image
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: Need to find a character - RUSTY SOLVED IT

Post by rustypup »

:lol: ... sheesh... can i introduce you to the sales force sometime... they think my second name's beelzebub... :twisted:
Most people would sooner die than think; in fact, they do so - Bertrand Russel
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Need to find a character - RUSTY SOLVED IT

Post by Tribble »

I don't believe that - you are amazing :mrgreen: Whenever I have needed help - you have come up with the solution :D Thanks.
Image
D3PART3D
Starbound's Dear
Posts: 16294
Joined: 01 Dec 2004, 02:00
Contact:

Re: Need to find a character - RUSTY SOLVED IT

Post by D3PART3D »

rustypup wrote:they think my second name's beelzebub...
It isn't? :?
Ceterum autem censeo Samsung Mobile esse delendam.

When something is important enough, you do it even if the odds are not in your favor.
- Elon Musk
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Need to find a character - RUSTY SOLVED IT

Post by Tribble »

I shall start the Rustypup Appreciation Society :wink:
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: Need to find a character - RUSTY SOLVED IT

Post by RuadRauFlessa »

Would you mind posting the complete query Tribble?
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Need to find a character - RUSTY SOLVED IT

Post by Tribble »

Wow - it is large. Do you want just that formula or the SQL version or a snapshot view of it?
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: Need to find a character - RUSTY SOLVED IT

Post by RuadRauFlessa »

SQL creation script of your table and the query you run to retrieve the data. Should do fine.

And Tribble large is a matter of perspective when speaking in DB terms. For one person 100MB table might be large. For me a table with over 2.4billion records spanning over 3TB is big. And yes that is only for one table. Had to work on that thing and believe me your perspective of big really changes drastically. You have to really look at optimization on your queries. The simplest of a count() statement on the table took over 2 hours if you don't do it correctly.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Need to find a character - RUSTY SOLVED IT

Post by Tribble »

Well all the ements of the qualification are listed next to each other - there are 19 of them. They contain a G = Exam 1 is necessary, S = Exam 1 is not necessary and N = does not exist for that qualification. Exam 1 need only be written once for each qualification - no matter what subcategory is taken - provided that it is marked with a G.

I usedCombine: [1,01] & [1,02] & [1,03] & [1,04] & [1,05] & [1,06] & [1,07] & [1,08] & [1,09] & [1,10] & [1,11] & [1,12] & [1,13] & [1,14] & [1,15] & [1,16] & [1,17] & [1,18] & [1,19] to get all the Gs, Ss and Ns together.

Then I used G: InStr([Combine],"g") (Rusty's help) to see if there was at least one G in the 19 subgroups.

Then I used Exam1: IIf([G]>0,"Yes","No") to determine whether an Exam 1 was required for that qualification.

Each subgroup has a compulsory exam 2 which has to be written. Only once all the subgroups have been passed - does the qualification register as complete.

They also have deadline dates for each exam - depending on when they were employed. I use the year and the iif function for those.



Hope that helped.
Image
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Need to find a character - RUSTY SOLVED IT

Post by Tribble »

RuadRauFlessa, :shock: wow that is big. In comparison my databases are miniscule :lol:

Here is the SQL

Code: Select all

SELECT dbo_tblApprovedQualifications.QualID, dbo_tblApprovedQualifications.Category, dbo_tblApprovedQualifications.[Qualification Title], dbo_tblApprovedQualifications.[ID Number], dbo_tblApprovedQualifications.[Training Provider], dbo_tblApprovedQualifications.Level, dbo_tblApprovedQualifications.Credits, dbo_tblApprovedQualifications.[1,01], dbo_tblApprovedQualifications.[1,02], dbo_tblApprovedQualifications.[1,03], dbo_tblApprovedQualifications.[1,04], dbo_tblApprovedQualifications.[1,05], dbo_tblApprovedQualifications.[1,06], dbo_tblApprovedQualifications.[1,07], dbo_tblApprovedQualifications.[1,08], dbo_tblApprovedQualifications.[1,09], dbo_tblApprovedQualifications.[1,10], dbo_tblApprovedQualifications.[1,11], dbo_tblApprovedQualifications.[1,12], dbo_tblApprovedQualifications.[1,13], dbo_tblApprovedQualifications.[1,14], dbo_tblApprovedQualifications.[1,15], dbo_tblApprovedQualifications.[1,16], dbo_tblApprovedQualifications.[1,17], dbo_tblApprovedQualifications.[1,18], dbo_tblApprovedQualifications.[1,19], [1,01] & [1,02] & [1,03] & [1,04] & [1,05] & [1,06] & [1,07] & [1,08] & [1,09] & [1,10] & [1,11] & [1,12] & [1,13] & [1,14] & [1,15] & [1,16] & [1,17] & [1,18] & [1,19] AS Combine, InStr([Combine],"g") AS G, IIf([G]>0,"Yes","No") AS Exam1
FROM dbo_tblApprovedQualifications;
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: Need to find a character - RUSTY SOLVED IT

Post by RuadRauFlessa »

Ok do you have a create script for the table structure?
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Need to find a character - RUSTY SOLVED IT

Post by Tribble »

You mean this?

Code: Select all

USE [FAP]
GO
/****** Object:  Table [dbo].[tblApprovedQualifications]    Script Date: 02/04/2009 12:55:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblApprovedQualifications](
	[QualID] [int] NOT NULL,
	[Category] [varchar](5) NULL,
	[Qualification Title] [varchar](100) NULL,
	[ID Number] [int] NULL,
	[Training Provider] [varchar](100) NULL,
	[Level] [smallint] NULL,
	[Credits] [int] NULL,
	[1,01] [varchar](2) NULL,
	[1,02] [varchar](2) NULL,
	[1,03] [varchar](2) NULL,
	[1,04] [varchar](2) NULL,
	[1,05] [varchar](2) NULL,
	[1,06] [varchar](2) NULL,
	[1,07] [varchar](2) NULL,
	[1,08] [varchar](2) NULL,
	[1,09] [varchar](2) NULL,
	[1,10] [varchar](2) NULL,
	[1,11] [varchar](2) NULL,
	[1,12] [varchar](2) NULL,
	[1,13] [varchar](2) NULL,
	[1,14] [varchar](2) NULL,
	[1,15] [varchar](2) NULL,
	[1,16] [varchar](2) NULL,
	[1,17] [varchar](2) NULL,
	[1,18] [varchar](2) NULL,
	[1,19] [varchar](2) NULL,
 CONSTRAINT [PK_tblApprovedQualifications] PRIMARY KEY CLUSTERED 
(
	[QualID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
Please remember I am teaching myself SQL Manager so have no clue what I am doing here lol
Image
UrBaN
Registered User
Posts: 12811
Joined: 02 Feb 2005, 02:00
Location: JHB East
Contact:

Re: Need to find a character - RUSTY SOLVED IT

Post by UrBaN »

Tribble wrote:You mean this?

Code: Select all

USE [FAP]
:lol: :lol: :lol: :lol: :lol: :lol:
You just invited jamin to this thread :lol: :lol: :lol:
Image
to ápeiro anima
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Need to find a character - RUSTY SOLVED IT

Post by Tribble »

:lol: :lol: FAP - Fit and Proper :lol:
Image
UrBaN
Registered User
Posts: 12811
Joined: 02 Feb 2005, 02:00
Location: JHB East
Contact:

Re: Need to find a character - RUSTY SOLVED IT

Post by UrBaN »

Tribble wrote: :lol: :lol: FAP - Fit and Proper :lol:
Should hope it fits :whistling:

Nice code BTW ;) :twisted:
Image
to ápeiro anima
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: Need to find a character - RUSTY SOLVED IT

Post by RuadRauFlessa »

You mean SSMS (SQL Server Management Studio). It is a good idea. It has a nice query analyzer you could use. do yourself a fav and run the query but select the execution plan button on the toolbar. It will show you an extra tab after showing the result of the query. You can user this nicely to eliminate any bottlenecks.

First of all as a rule of thumb you can always use a nolock or readover option on the table if you are only going to select data from it. just add WITH (NOLOCK) after the table name. This will basically read from the table without causing a read-lock. It is about 80% faster by my experience. If something is being written at the time you run the query it will however omit the data which has not been commited yet. In other words any record with a write-lock on it will not be taken into concideration for the query result.

Further than that the query looks solid. You have not included a WHERE clause. Will you add it at a stage or is it going to remain as is?
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Need to find a character - RUSTY SOLVED IT

Post by Tribble »

Thanks - that looks helpful. I shall read up about it. I have litterally been thrown in the deepend with this. I created an Access database and we then coverted it to SQL as their network was far too slow to run it. So I am working my way around it - and seeing what does what. Any help is required.

I just built the query to specify which exams are needed - and yes - I will be adding a where clause later. Still trying to work out how all this will best fit together. Also just been told that they got it wrong - everyone writes Exam 1 but only the Gs need exam 2 - so have to go and change it :lol:

This is just one of many many tables :shock:
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: Need to find a character - RUSTY SOLVED IT

Post by RuadRauFlessa »

I can imagine there will be a bunch of others. When you start writing the where clauses you might want to think about adding indexes to the table. I noted that the only index is the key field (Primary Index). Using indexes is highly underrated and underestimated by a lot of people. Also try and restrict the where clause lookup values to int. It is much quicker and easier to index as well.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
Synthesis
Registered User
Posts: 14517
Joined: 25 May 2006, 02:00
Location: Location, Location
Contact:

Re: Need to find a character - RUSTY SOLVED IT

Post by Synthesis »

If only the UNSOLVED threads could go on for as long as the SOLVED threads, then this would indeed be a better tech-related forum.
Image
Locked