Regular Expression, SubString...? - 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

General SQL Server Forums

New to SQL Server Programming

Regular Expression, SubString...?

Author

Topic

kmf113
Starting Member

2 Posts

Posted - 2011-08-29 : 15:32:06

Hello,

Basically I am trying to extract a username from a column containing a line of text. Not each field for that column will contain a username but when it does it will always be the last word and preceded with "by". Example = "This is some text by BOBBY".

This new functionality will be added on to a current SELECT statement. Seen below. The rows I get back were fine but now I don't want rows entered by the administrator I just want rows from all other users. The username I don't want to be found will be stored in a config table and not hardcoded. TSQL follows:

CURRENT SELECT STATEMENT


SELECT * FROM LOG_TABLE WHERE DATE_TIME >= '2009-01-08 04:10:20.0000000'
AND SERVER_ID = 1
AND CODE IN(Select CODES_TO_CHECK FROM CODE_TABLE)

LOG_TABLE definition


CREATE TABLE LOG_TABLE(
	[SERVER_ID] [numeric](3, 0) NOT NULL,
	[NAME] [nvarchar](80) NULL,
	[DATE_TIME] [datetime2](7) NOT NULL,
	[LOG_NUM] [numeric](4, 0) NOT NULL,
	[TEXT] [nvarchar](255) NULL,
	[CODE] [numeric](3, 0) NULL,
 CONSTRAINT [PK_LOG_TABLE] PRIMARY KEY CLUSTERED 
(
	[SERVER_ID] ASC,
	[DATE_TIME] ASC,
	[LOG_NUM] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CODE_TABLE definition


CREATE TABLE CODE_TABLE
(	
[CODES_TO_CHECK] [numeric](3, 0) NOT NULL,
 CONSTRAINT [PK_CODE_TABLE] PRIMARY KEY CLUSTERED 
(
	[MESSAGE_CODE_TO_BE_CHECKED] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Config table definition (containing username)


CREATE TABLE CONFIG_TABLE(
	[SERVER_ID] [numeric](3, 0) NOT NULL,
	[USERNAME_TO_IGNORE] [nvarchar](50) NULL,
 CONSTRAINT [PK_CONFIG_TABLE] PRIMARY KEY CLUSTERED 
(
	[SERVER_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

I assume I need to use something like substring,regex in a function or SP?
Just looking for a general idea on how to tackle this efficiently.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-29 : 16:53:00

not sure how efficient this would be but you could try this:


SELECT <explicit column list>
FROM LOG_TABLE l
left outer join config_table c
       on c.server_id = l.server_id
       and 'by ' + c.Username_to_ignore = right(l.[text], len(c.Username_to_ignore) + 3)
WHERE DATE_TIME >= '2009-01-08 04:10:20.0000000'
AND SERVER_ID = 1
AND CODE IN(Select CODES_TO_CHECK FROM CODE_TABLE)
and c.server_id is null

You should use an explicit column list instead of "*" and it is not good practice to use very generic object names or keywords like (date_time, text, code, and name)

Be One with the Optimizer
TG

kmf113
Starting Member

2 Posts

Posted - 2011-09-06 : 05:56:30

Thankyou.
this is very good and done what I needed it too :-)

I am using explicit column names now and I used words like "text" etc just to make it as simple as possible. Maybe confusing in hinde sight.

Thanks :-)

Kristen
Test

22859 Posts

Posted - 2011-09-06 : 08:53:18

Might be more efficient?


left outer join config_table c
       on c.server_id = l.server_id
       AND l.[text] LIKE '%by ' + c.Username_to_ignore

if you are sure that there is a space before "by", in the data, then include it here to improve the accuracy:


       AND l.[text] LIKE '% by ' + c.Username_to_ignore

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources