Match if one character different? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Match if one character different?

Author

Topic

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 02:42:32

I am looking for a partial match in a column.

I have an SProc that displays the column names for a given table, so I can easily Cut & Paste to make SELECT / INSERT / JOIN etc).

It takes a wildcard parameter so I don't have to type the whole table name

So I can type uspListCols FOO to display details for table "FooBar"

If I type uspListCols XXXX then if XXXX matches a single table I get the columns list etc., if it matches multiple tables I get a list of matching table names - so I can try again with a unique table name parameter.

However, we have Archive tables for nearly every table in our system, and their names different by one additional character.

I would like to ignore the Archive tables - so if XXXX matches two table names, and the only difference is the extra letter, than it treats that as a unique hit.

Our table names are all of the format

XXX_YYY_zzzzz - the main table
XXXa_YYY_zzzz - the archive table

The length of "XXX" varies - currently between 2 and 4 leading characters.


DECLARE	@strFind	varchar(20)

SELECT @strFind = 'zzzz'	-- Test 1 - Match XXX_YYY_zzzz but not XXX_YYY_zzzz
-- SELECT @strFind = 'XXX'	-- Test 2 - Ditto
-- SELECT @strFind = 'AA'	-- Test 3 - Match AA_CCC_dddd but not AAa_CCC_dddd
-- SELECT @strFind = 'EEE'	-- Test 4 - Match CD_EEE_ffff

DECLARE	@Temp TABLE
(
	T_ID	int IDENTITY(1,1) NOT NULL,
	T_Name	varchar(20)
)

INSERT INTO @Temp
SELECT 'XXX_YYY_zzzz' UNION ALL
SELECT 'XXXa_YYY_zzzz' UNION ALL
SELECT 'AA_CCC_dddd' UNION ALL	-- Matches "%A_%"
SELECT 'AAa_CCC_dddd' UNION ALL
SELECT 'CD_EEE_ffff'		-- No associated archive table

SELECT	*
FROM	@Temp
WHERE	T_Name LIKE '%' + @strFind + '%'
--	AND T_Name NOT IN ???

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-25 : 03:17:02

See if this works for all cases

SELECT * FROM
(
SELECT row_number() over (order by substring(T_Name,1,charindex('a_',T_Name))) sno,T_name
FROM @Temp
WHERE T_Name LIKE '%' + @strFind + '%'
) as t
WHERE sno=1

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-25 : 07:43:46

Are you able to make it work?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 09:10:12

Sorry, been busy ... hopefully I will get some slack time this afternoon. If I don't have to go to the bank that is where my "personal banker" works ... that will be an hour or two's round trip

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-03 : 01:53:53

Any feedback?
Are you still busy?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-03-03 : 02:20:48

Yeah, need to get to this. Been out of office 2 days, out again from this afternoon for another couple of days, real world getting in the way, sorry. But its on my list (although a nudge is always welcome

)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-03 : 02:41:16

quote:
Originally posted by Kristen

Yeah, need to get to this. Been out of office 2 days, out again from this afternoon for another couple of days, real world getting in the way, sorry. But its on my list (although a nudge is always welcome )

No problem. Work on it when you find enough time

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-15 : 06:29:11

Is this closed?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-15 : 06:57:46

[code]SELECT *
FROM @Temp
WHERE '_' + T_Name + '_' LIKE '%[_]' + @strFind + '[_]%'
AND SUBSTRING(T_Name, 1, CHARINDEX('_', T_name)) NOT LIKE '%a[_]%' COLLATE LATIN1_GENERAL_CS_AS[/code]

N 56°04'39.26"
E 12°55'05.63"

Kristen
Test

22859 Posts

Posted - 2010-06-15 : 09:22:13

quote:
Originally posted by madhivanan

Is this closed?

No, but not had time to revisit it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources