Need to find a character - RUSTY SOLVED IT
- Tribble
- Registered User
- Posts: 88465
- 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
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!
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.
- 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
instr()?
returns >0 if the substring is present
returns 0 if not..
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
- Tribble
- Registered User
- Posts: 88465
- 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
You are my saviour!!!!!!!!!!!!!!!!!!!!!!!!! You are amazing and wonderful and awesome and spectacular and just THE cleverest person on this entire forum!!!!!!!!!!!!!!
- Tribble
- Registered User
- Posts: 88465
- 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
Thank you so much. It is working beautifully.
Can someone lock the thread now please?
Can someone lock the thread now please?
Re: Need to find a character in a string - MS Access
No. We must now spam it to death.Tribble wrote:Can someone lock the thread now please?
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
When something is important enough, you do it even if the odds are not in your favor.
- Elon Musk
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: Need to find a character - RUSTY SOLVED IT
What language is it in Tribble. Just as a reference though.
- Spoiler (show)
- Tribble
- Registered User
- Posts: 88465
- 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
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() Now I do.
- 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
... sheesh... can i introduce you to the sales force sometime... they think my second name's beelzebub...
Most people would sooner die than think; in fact, they do so - Bertrand Russel
- Tribble
- Registered User
- Posts: 88465
- 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
I don't believe that - you are amazing Whenever I have needed help - you have come up with the solution Thanks.
Re: Need to find a character - RUSTY SOLVED IT
It isn't?rustypup wrote:they think my second name's beelzebub...
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
When something is important enough, you do it even if the odds are not in your favor.
- Elon Musk
- Tribble
- Registered User
- Posts: 88465
- 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
I shall start the Rustypup Appreciation Society
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: Need to find a character - RUSTY SOLVED IT
Would you mind posting the complete query Tribble?
- Spoiler (show)
- Tribble
- Registered User
- Posts: 88465
- 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
Wow - it is large. Do you want just that formula or the SQL version or a snapshot view of it?
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: Need to find a character - RUSTY SOLVED IT
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.
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.
- Spoiler (show)
- Tribble
- Registered User
- Posts: 88465
- 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
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.
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.
- Tribble
- Registered User
- Posts: 88465
- 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
RuadRauFlessa, wow that is big. In comparison my databases are miniscule
Here is the SQL
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;
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: Need to find a character - RUSTY SOLVED IT
Ok do you have a create script for the table structure?
- Spoiler (show)
- Tribble
- Registered User
- Posts: 88465
- 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
You mean this?
Please remember I am teaching myself SQL Manager so have no clue what I am doing here lol
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
Re: Need to find a character - RUSTY SOLVED IT
Tribble wrote:You mean this?Code: Select all
USE [FAP]
You just invited jamin to this thread
to ápeiro anima
- Tribble
- Registered User
- Posts: 88465
- 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
FAP - Fit and Proper
Re: Need to find a character - RUSTY SOLVED IT
Should hope it fitsTribble wrote: FAP - Fit and Proper
Nice code BTW
to ápeiro anima
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: Need to find a character - RUSTY SOLVED IT
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?
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?
- Spoiler (show)
- Tribble
- Registered User
- Posts: 88465
- 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
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
This is just one of many many tables
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
This is just one of many many tables
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: Need to find a character - RUSTY SOLVED IT
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.
- Spoiler (show)
- Synthesis
- Registered User
- Posts: 14517
- Joined: 25 May 2006, 02:00
- Location: Location, Location
- Contact:
Re: Need to find a character - RUSTY SOLVED IT
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.