asked on

regexp / substring -- Microsoft SQL 2005 ?

Below "regexp_substr" works in Oracle.

What same type of logic works in Microsoft SQL 2005
so it appears like the attached TWO columns ?
------------------------------------------------------
SELECT WO_OpID
-- regexp_substr(WO_OpID, '^[^_]+') AS qty
FROM Time_Scanning
WHERE (WO_OpID IS NOT NULL)
s006.jpg

Jim Horn

To remove all underscores from column WO_OpID...

SELECT     WO_OpID,  REPLACE(WO_OpID, '_', '') as qty
FROM         Time_Scanning
WHERE     (WO_OpID IS NOT NULL)

Open in new window

btw nice mockup

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

QuinnDex

SQL 2005 doesnt have regex function as standard but it is possible to add it using CLR and its not hard to do, see the link below for an example

http://justgeeks.blogspot.co.uk/2008/08/adding-regular-expressions-regex-to-sql.html

finance_teacher

ASKER

Below works good.

SELECT WO_OpID,
CASE WHEN CHARINDEX('_', WO_OpID) > 0 THEN LEFT(WO_OpID, CHARINDEX('_', WO_OpID) - 1) ELSE '' END AS left_side,
CASE WHEN CHARINDEX('_', WO_OpID) > 0 THEN RIGHT(WO_OpID, LEN(WO_OpID) - CHARINDEX('_', WO_OpID)) ELSE '' END AS right_side
FROM Time_Scanning
WHERE (WO_OpID IS NOT NULL)

Jim Horn

Thanks for the grade. Good luck with your project. -Jim

btw if you're used to RegEx, QuinnDex's article could be used to create a SQL function that mimics it. Worth exploring if that's your comfort level.