matching result from two different columns
-
I have a mysql table (tblNames)with columns: colA, colB, MiddleName, WholeName. with data like: colA = aaa colB = bbb MiddleName = Alan WholeName = JohnAlanSmith I would like to get a result of all the data where the MiddleName equals the middle part of the WholeName column starting at 4 and a length of 4 characters, for example where the MiddleName column contains "Alan" and the Mid Function result of the WholeName column equals "Alan", where they match. I am using following Mid Function to get the middle part of the WholeName column:
SELECT Mid([WholeName ],4,7) AS "MatchingResult"
FROM tblNames;The result could be a query or a new table.
-
I have a mysql table (tblNames)with columns: colA, colB, MiddleName, WholeName. with data like: colA = aaa colB = bbb MiddleName = Alan WholeName = JohnAlanSmith I would like to get a result of all the data where the MiddleName equals the middle part of the WholeName column starting at 4 and a length of 4 characters, for example where the MiddleName column contains "Alan" and the Mid Function result of the WholeName column equals "Alan", where they match. I am using following Mid Function to get the middle part of the WholeName column:
SELECT Mid([WholeName ],4,7) AS "MatchingResult"
FROM tblNames;The result could be a query or a new table.
And? What problem are you having?
The difficult we do right away... ...the impossible takes slightly longer.
-
And? What problem are you having?
The difficult we do right away... ...the impossible takes slightly longer.
I realized that I can use a make-table query to get the data from the middle of the string in one column and make a new table with all the data plus the new column with the data extracted with the mid function. However, the example I gave was just a simple example to make it clear, the problem I am having is the mid function starts at a position counting from the left but the left side of my string before the data I need to extract varies, the right side of the string is always the same, for example, in this string: abcde/fghi/1234567/0005555555-11-000001.txt I need to extract the "0005555555" but the string ahead of it, "/1234567/" is sometimes 6 characters and sometimes 7 characters long.
-
I realized that I can use a make-table query to get the data from the middle of the string in one column and make a new table with all the data plus the new column with the data extracted with the mid function. However, the example I gave was just a simple example to make it clear, the problem I am having is the mid function starts at a position counting from the left but the left side of my string before the data I need to extract varies, the right side of the string is always the same, for example, in this string: abcde/fghi/1234567/0005555555-11-000001.txt I need to extract the "0005555555" but the string ahead of it, "/1234567/" is sometimes 6 characters and sometimes 7 characters long.
I'm not up on all the string functions in MySQL, but it would seem that what you need to do is first split the string according to the "/" delimiter. Look for a string function that will find an occurrence of "/" starting at a certain point in the string, then use that index value to begin your substr function.
The difficult we do right away... ...the impossible takes slightly longer.
-
I realized that I can use a make-table query to get the data from the middle of the string in one column and make a new table with all the data plus the new column with the data extracted with the mid function. However, the example I gave was just a simple example to make it clear, the problem I am having is the mid function starts at a position counting from the left but the left side of my string before the data I need to extract varies, the right side of the string is always the same, for example, in this string: abcde/fghi/1234567/0005555555-11-000001.txt I need to extract the "0005555555" but the string ahead of it, "/1234567/" is sometimes 6 characters and sometimes 7 characters long.
-
