February 13, 2016 at 8:35 pm
I'm stumped. I have two tables. The first table has a field where I need to extract an alpha-numeric string where I can use the substring function to achieve that . The second table is where I need to extract that same alpha-numeric string but it is embedded within the field but not always in the same position. How do I use either SCAN or SEEK to extract that string so I can perform a match with the same string from the first table. Any assistance would be greatly appreciated. Thanks.
February 14, 2016 at 1:27 am
Err... Seek and scan are SQL's two internal operations for accessing indexes, they're not something you explicitly use.
What exactly are you trying to do? Post table definitions (as CREATE TABLE statements) and sample data (as INSERT statements) please
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2016 at 8:41 am
[Err... Seek and scan are SQL's two internal operations for accessing indexes, they're not something you explicitly use.
What exactly are you trying to do? Post table definitions (as CREATE TABLE statements) and sample data (as INSERT statements) please]
I need to extract the same string from two tables. I can use the SUBSTRING function to pull the alpha-numeric sequence from the first table since it is always in the same position in the field but I need to pull the same alpha-numeric sequence from a text field in the second table and the sequence is not always in the same position within the field. What code do I use so I can match the alpha-numeric sequence between the two tables? Any assistance would be greatly appreciated. Thanks.
February 14, 2016 at 9:30 am
GilaMonster (2/14/2016)
What exactly are you trying to do? Post table definitions (as CREATE TABLE statements) and sample data (as INSERT statements) please
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2016 at 10:08 am
GilaMonster (2/14/2016)
GilaMonster (2/14/2016)
What exactly are you trying to do? Post table definitions (as CREATE TABLE statements) and sample data (as INSERT statements) please
... plus the expected results
February 14, 2016 at 3:37 pm
greg6363 (2/14/2016)
[Err... Seek and scan are SQL's two internal operations for accessing indexes, they're not something you explicitly use.What exactly are you trying to do? Post table definitions (as CREATE TABLE statements) and sample data (as INSERT statements) please]
I need to extract the same string from two tables. I can use the SUBSTRING function to pull the alpha-numeric sequence from the first table since it is always in the same position in the field but I need to pull the same alpha-numeric sequence from a text field in the second table and the sequence is not always in the same position within the field. What code do I use so I can match the alpha-numeric sequence between the two tables? Any assistance would be greatly appreciated. Thanks.
You'll need to use a mid-string LIKE or a CHARINDEX to find the string in the table where the position is unknown. There is no way that will occur with any decent performance. The entire column will need to be scanned.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2016 at 7:32 am
With the value being in a known position in the first table you could preprocess that table to extract the string you require into a new column and create an index on that new column which could at least allow a seek on that.
Would depend on whether the overhead for creating the new column and indexing it is lower than the cost of the scan but if the table is being used for many such joins then it probably is.
I can't see any option for the text field though.
February 24, 2016 at 9:36 am
As an example, here's DDL with data.
CREATE TABLE dbo.MyTable ( mystring VARCHAR(200) );
GO
INSERT dbo.MyTable
( mystring )
VALUES
( 'This is a 7' )
, ( 'There is a 7 in this string' )
, ( 'Why must 7 always be here' )
, ( 'No 7s here. JK' )
, ( 'My 7s here' )
, ( 'An 7s here.' )
, ( 'On 7s here.' );
GO
SELECT
'SetPosition' = SUBSTRING(mystring, 4, 1)
, 'FindThe7' = SUBSTRING(mystring, CHARINDEX('7', mystring), 1)
FROM
dbo.MyTable AS mt;
DROP TABLE dbo.MyTable;
Note that in the first 3 rows, the 7 varies by position. The next 4 are fixed, so the first column in my result set works for only the 4 rows that have a fixed position. The second column finds the pattern no matter where it is.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply