Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Is this possible - SQL JOIN ?

Answered Is this possible - SQL JOIN ?

  • Wednesday, February 20, 2013 7:56 PM
     
     
    Sign In to Vote
    0

    Hi,

    I have 2 tables in a SQL DB:

    Table 1 called 'dbp.FAIL' contains a column called 'MsgText' with the following data :

    17294: 029514: Dec 11 10:06:54: %DOT1X-5-FAIL: Authentication failed for client (d4be.d96b.c87a) on Interface Fa0/6 AuditSessionID AC1E010C00000419AD57A754.

    Table 2 called 'Lookup' contains 2 columns called 'MAC' and 'Hostname' :

    MAC                             HOSTNAME

    d4be.d96b.c87a           PC01

    What I want to do is lookup the value (d4be.d96b.c87a) in Table 1 and replace it from the corresponding value from Table 2, I.e the MsgText column would then be :

    17294: 029514: Dec 11 10:06:54: %DOT1X-5-FAIL: Authentication failed for client (PC01) on Interface Fa0/6 AuditSessionID AC1E010C00000419AD57A754.

    Is this possible ?

    Thanks

    Bill

     

     

All Replies

  • Wednesday, February 20, 2013 8:04 PM
    Moderator
     
      Has Code
    Sign In to Vote
    1

    You can use REPLACE function to start. A simple sample:

    Create table dbo.FAIL ( id int, MsgText varchar(4000))
Create table Lookup (Mac varchar(50), HOSTNAME varchar(50))

insert into Lookup values ('d4be.d96b.c87a','PC01')

insert into FAIL values (1,'17294: 029514: Dec 11 10:06:54: %DOT1X-5-FAIL: Authentication failed for client (d4be.d96b.c87a) on Interface Fa0/6 AuditSessionID AC1E010C00000419AD57A754.')
 
select replace(MsgText,a.Mac, a.HOSTNAME) as MsgText from Lookup a, dbo.FAIL b

drop table lookup
drop table dbo.FAIL

     
  • Wednesday, February 20, 2013 8:07 PM
     
      Has Code
    Sign In to Vote
    0

    Use the Replace function, for example,

    Declare @Fail Table(MsgText varchar(200));
Insert @Fail(MsgText)
Select '17294: 029514: Dec 11 10:06:54: %DOT1X-5-FAIL: Authentication failed for client (d4be.d96b.c87a) on Interface Fa0/6 AuditSessionID AC1E010C00000419AD57A754.';

Declare @Table2 Table(MAC varchar(16), HostName varchar(50));
Insert @Table2(MAC, HostName)
Select 'd4be.d96b.c87a', 'PC01';

Select Replace(f.MsgText, t.MAC, t.HostName) As MsgText
From @Fail f
Inner Join @Table2 t On f.MsgText Like '%' + t.MAC + '%';

    Tom

     
  • Wednesday, February 20, 2013 8:07 PM
     
      Has Code
    Sign In to Vote
    0

    Something like this (untested)

    UPDATE f
SET MsgText = REPLACE(MsgText,l.MAC,l.HOSTNAME)
FROM dbo.FAIL f
JOIN dbo.Lookup l
	ON f.MsgText like '%' + l.MAC + '%'

    Chuck Pedretti | Magenic – North Region | magenic.com

     
  • Wednesday, February 20, 2013 8:28 PM
     
     
    Sign In to Vote
    0

    Thanks,

    I forgot to say that there will be lots of different values in the source table / column, i.e. not just (d4be.d96b.c87a).

    I guess it would need something extra to get the value between the ( ) and then look this up in the 'lookup table' ?

    Bill

     
  • Wednesday, February 20, 2013 9:04 PM
     
      Has Code
    Sign In to Vote
    0

    Then create and use a function

    CREATE FUNCTION fn_ReplaceHostLookup
(
	@Text NVARCHAR(500)
)
RETURNS NVARCHAR(500)
AS
BEGIN
	DECLARE @Tab TABLE
	(
		MAC NVARCHAR(50),
		HostName NVARCHAR(50)
	)
	
	INSERT INTO @Tab SELECT Mac, HostName FROM [Lookup]
	
	DECLARE @Out NVARCHAR(500)
	DECLARE @TempMac NVARCHAR(50)
	DECLARE @TempHostName NVARCHAR(50)
	SET @Out = @Text
	
	WHILE EXISTS (SELECT * FROM @Tab)
	BEGIN
		SELECT TOP 1 @TempMac = MAC, @TempHostName = HostName FROM @Tab
		SET @Out = REPLACE(@Out, @TempMac, @TempHostName)
		DELETE FROM @Tab WHERE Mac = @TempMac
	END
	
	RETURN @Out
END
GO

select msgtext, dbo.fn_ReplaceHostLookup(msgtext) from fail

     
  • Thursday, February 21, 2013 3:54 AM
     
     Answered Has Code
    Sign In to Vote
    0

    And the above techniques will work with multiple values, so, for example

    Declare @Fail Table(MsgText varchar(200));
Insert @Fail(MsgText)
Select '17294: 029514: Dec 11 10:06:54: %DOT1X-5-FAIL: Authentication failed for client (d4be.d96b.c87a) on Interface Fa0/6 AuditSessionID AC1E010C00000419AD57A754.'
Union All Select 'blah blah blah (abcd.1234.fedc) blah blah)'
Union All Select 'yada yada (1234.5678.90ab) yada yada yada)';

Declare @Table2 Table(MAC varchar(16), HostName varchar(50));
Insert @Table2(MAC, HostName)
Select 'd4be.d96b.c87a', 'PC01'
Union All Select 'abcd.1234.fedc', 'PC02'
Union All Select '9876.5432.1010', 'PC03'
Union All Select '1234.5678.90ab', 'PC99';

Select Replace(f.MsgText, t.MAC, t.HostName) As MsgText
From @Fail f
Inner Join @Table2 t On f.MsgText Like '%(' + t.MAC + ')%';
    Tom

     
  • Thursday, February 21, 2013 6:29 AM
     
      Has Code
    Sign In to Vote
    0

    Hello Bill,

    Try this code below,

    Declare @Fail Table(MsgText varchar(200));
Insert @Fail(MsgText)
Select '17294: 029514: Dec 11 10:06:54: %DOT1X-5-FAIL: Authentication failed for client (d4be.d96b.c87a) on Interface Fa0/6 AuditSessionID AC1E010C00000419AD57A754.';

Declare @Table2 Table(MAC varchar(16), HostName varchar(50));
Insert @Table2(MAC, HostName)
Select 'd4be.d96b.c87a', 'PC01';

select 
REPLACE(MsgText,MAC,HOSTNAME)
from @Fail a
join @Table2 b on 
SUBSTRING(msgtext,charindex('(',msgtext)+1,datalength(msgtext)-charindex('(',msgtext)-CHARINDEX(')',reverse(msgtext)))
= b.MAC

    Dineshkumar Please "Mark as Answer" if my post answers your question or "Vote as Helpful" if it helps you