Not Like IN ( Select ...) - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Not Like IN ( Select ...)

Author

Topic

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2007-10-03 : 17:16:45

Hi,

I have 2 Questions:

Background:

I have 2 tables t1 & t2, Each 1 having a field F1 varchar(50), among many others.

Q1. I need to remove the leading zeros of the Field when Queried. The data may contain letters, hiphen etc
eg.

000A123400 --> A123400
563400 --> 563400
0102 --> 102
00-00234 --> -00234

Q2. I need to compare the results of above with the data in the F1 field of the second table for NOT LIKE

eg.
if t1.F1 has 0102 (returns 102 from above), I do not want to get, 011020
and so on.

First Table has 500 records, the second table has 75000 records. This is to be done only few times (so time - efficiency may not be issues).
ie. I can wait for 10 minutes until the query finishes running.

Thanks

Srinika

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-03 : 19:56:48

SELECT REPLACE(LTRIM(REPLACE(F1, '0', ' ')), ' ', '0') FROM t1

The above will trim the leading zeros, but I am not sure from your post what you are looking to return in the SELECT, do you want to return the values from t2 where the values do not contain the trimmed values from t1 anywhere in the string?

Future guru in the making.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-04 : 01:52:38

[code]DECLARE @Sample TABLE (Data VARCHAR(30))

INSERT @Sample
SELECT '000A123400' UNION ALL
SELECT '563400' UNION ALL
SELECT '0102' UNION ALL
SELECT '00-00234'

SELECT Data,
SUBSTRING(Data, PATINDEX('%[^0]%', Data), 30)
FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-04 : 02:31:34

quote:
Originally posted by Zoroaster

SELECT REPLACE(LTRIM(REPLACE(F1, '0', ' ')), ' ', '0') FROM t1

The above will trim the leading zeros, but I am not sure from your post what you are looking to return in the SELECT, do you want to return the values from t2 where the values do not contain the trimmed values from t1 anywhere in the string?

Future guru in the making.

With this approach, all spaces would be replaced by 0 which is incorrect

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-04 : 02:34:08

[code]DECLARE @Sample TABLE (Data VARCHAR(30))

INSERT @Sample
SELECT '000A123400' UNION ALL
SELECT '563400' UNION ALL
SELECT '5634 A' UNION ALL
SELECT '0102' UNION ALL
SELECT '00-00234'

SELECT Data AS Original,
SUBSTRING(Data, PATINDEX('%[^0]%', Data), 30) AS Peso,
REPLACE(LTRIM(REPLACE(Data, '0', ' ')), ' ', '0') AS Zoroaster
FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2007-10-04 : 07:35:57

Hi Peso, Thanks for the answer for the 1st Q.
It worked.

Is there a way to resolve my 2nd Q?

Srinika

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-04 : 11:41:11

Good catch Madhivanan, I will update my own library with Peso's version.

Future guru in the making.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-04 : 11:42:42

quote:
Originally posted by Srinika

Hi Peso, Thanks for the answer for the 1st Q.
It worked.

Is there a way to resolve my 2nd Q?

Srinika

Srinika, did you see my questions earlier regarding the requirement for your 2nd q?

"I am not sure from your post what you are looking to return in the SELECT, do you want to return the values from t2 where the values do not contain the trimmed values from t1 anywhere in the string?"

Future guru in the making.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2007-10-04 : 16:01:07

Yes Zoroaster
[I want to return the values from t2 where the values do not contain the trimmed values from t1 anywhere in the string of t2.F1]

Something Like following( I know its incorrect)

Select * from t2 where 
t2.f1 column not having anything Like 
(returns from the previous query)

Srinika

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-04 : 23:02:41

This is tricky, I'm hoping Peso or someone will step in with some tricks to knock this out for you but if not I will try to work on it tomorrow when I can play with it.

Future guru in the making.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-05 : 11:44:04

I can't figure out an easy way to do this unfortunately. Still, would like to see if there is one. I got close using Dynamic SQL but it was convoluted.
Anyone?

Future guru in the making.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2007-10-05 : 12:11:35

Can CONTAINS be used ??
Good if not with a cursor, I'd go for that as my last option.

Srinika

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-10-05 : 13:16:47

[code]DECLARE @t1 TABLE
(
value varchar(30) NOT NULL
)
INSERT INTO @t1
SELECT '000A123400' UNION ALL
SELECT '563400' UNION ALL
SELECT '0102' UNION ALL
SELECT '00-00234'

DECLARE @t2 TABLE
(
value varchar(30) NOT NULL
)
INSERT INTO @t2
SELECT '011020' UNION ALL
SELECT 'AAA102777' UNION ALL
SELECT 'GGG563400BBB' UNION ALL
SELECT 'GGG5634BBB'

-- either
SELECT T2.*
FROM @t2 T2
LEFT JOIN @t1 T1
ON T2.Value LIKE '%' + SUBSTRING(T1.value, PATINDEX('%[^0]%', T1.value), 30) + '%'
WHERE T1.value IS NULL

-- or
SELECT *
FROM @t2 T2
WHERE NOT EXISTS (
SELECT *
FROM @t1 T1
WHERE T2.Value LIKE '%' + SUBSTRING(T1.value, PATINDEX('%[^0]%', T1.value), 30) + '%'
)[/code]

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-05 : 14:28:07

Very nice Ifor, I tried doing it with the first method but didn't think of putting in the IS NULL.

Future guru in the making.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2007-10-09 : 09:49:52

Hey Ifor,

that works pretty well.

Thanks.

Also thanks Zoroaster & others

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources