Parse semicolon. field

[aajaydee]

I have following query
I need to parse field name "l.ErrorMsgEntry" which has long string all I'm interesting in to get the value left side of ';' (semi colon)

I try but it's not working any help
SELECT COUNT(l.ErrorMsgEntry) AS Error_Qunatity, parsename((l.ErrorMsgEntry,';'), 1)
FROM tblLogEntryTypes AS lt RIGHT OUTER JOIN
tblLogEntries AS l ON lt.LogEntryTypeID = l.LogEntryTypeID
GROUP BY l.ErrorMsgEntry

 

[SQLDenis]

replace parsename((l.ErrorMsgEntry,';'), 1)

with
LEFT(l.ErrorMsgEntry, CHARINDEX(l.ErrorMsgEntry,';')-1)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

SELECT COUNT(l.ErrorMsgEntry) AS Error_Qunatity, 
       Left(l.ErrorMsgEntry, CharIndex(';', l.ErrorMsgEntry) - 1)
FROM   tblLogEntryTypes AS lt 
       RIGHT OUTER JOIN tblLogEntries AS l 
          ON lt.LogEntryTypeID = l.LogEntryTypeID
GROUP BY l.ErrorMsgEntry

Parsename is a 'trick' that can sometimes be used user certain circumstances, but is probably not the best choice here.

To help you unserstand about parsename, take a look at this:

Select parsename('127.0.0.1', 4),
       parsename('127.0.0.1', 3),
       parsename('127.0.0.1', 2),
       parsename('127.0.0.1', 1)

Select parsename('a.b.c.d', 4),
       parsename('a.b.c.d', 3),
       parsename('a.b.c.d', 2),
       parsename('a.b.c.d', 1)

Select parsename('a.b.c.d.e.f', 1)

Notice how the last one gives NULL when there are more than 4 parts to the value.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[aajaydee]

Hi SQLDenis
when I tried your suggestion I get this error

Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

 

[gmmastros]

Try my suggestion. Denis had the parameters out of order.

CharIndex([!]';', l.ErrorMsgEntry[/!])

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[aajaydee]

Thank you guys very much
it is working now