CODEWITHSUNDEEP
sqldb2
Angel Antonio Rodriguez
Angel Antonio Rodriguez

Reputation: 21

in SQL how can I remove the first 3 characters on the left and everything on the right after an specific character

In SQL how can I remove (from displaying on my report no deleting from database) the first 3 characters (CN=) and everything after the comma that is followed by "OU" so that I am left with the name and last name in the same column? for example:

CN=Tom Chess,OU=records,DC=1234564786_data for testing, 1234567
CN=Jack Bauer,OU=records,DC=1234564786_data for testing, 1234567
CN=John Snow,OU=records,DC=1234564786_data for testing, 1234567
CN=Anna Rodriguez,OU=records,DC=1234564786_data for testing, 1234567

Desired display:

Tom Chess
Jack Bauer
John Snow
Anna Rodriguez

I tried playing with TRIM but I don't know how to do it without declaring the position and with names and last names having different lengths I really don't know how to handle that.

Thank you in advance

Update: I wonder about an approach of using Locate to match the position of the comma and then feed that to a sub-string. Not sure if a approach like would work and not sure how to put the syntax together. What do you think? will it be a feasible approach?

Upvotes: 2

Views: 978

Answers (7)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7181

If input str is wellformed (i.e. looks like your sample data without any additional tokens such as space), you could use something like:

substr(str,locate('CN=', str)+length('CN='), locate(',', str)-length('CN=')-1)

If your Db2 version support REGEXP, that's a better choice.

Upvotes: 0

Mark Barinstein
Mark Barinstein

Reputation: 12339

Db2 11.x for LUW: 

with tab (str) as (values
  '  CN  = Tom Chess  , OU = records,DC=1234564786_data for testing, 1234567'
, 'CN=Jack Bauer,OU=records,DC=1234564786_data for testing, 1234567'
, 'CN=John Snow,OU=records,DC=1234564786_data for testing, 1234567'
, 'CN=Anna Rodriguez,OU=records,DC=1234564786_data for testing, 1234567'
)
select REGEXP_REPLACE(str, '^\s*CN\s*=\s*(.*)\s*,\s*OU\s*=.*', '\1')
from tab;

Note, that such a regex pattern allows an arbitrary number of spaces as in the 1-st record of example above.

Upvotes: 1

Jim Castro
Jim Castro

Reputation: 894

On my version of DB2 for Z/OS CHARINDEX throws a syntax error. Here are two ways to work around that.

SUBSTRING(ColumnName, 4, INSTR(ColumnName,',',1) - 4)
SUBSTRING(ColumnName, 4, LOCATE_IN_STRING(ColumnName,',') - 4)

I should add that the version is V12R1

Upvotes: 0

user330315
user330315

Reputation:

In Postgres, you could use split_part() assuming no name contains a ,

select substr(split_part(the_column, ',', 1), 4)
from ...

Upvotes: 1

jefftrotman
jefftrotman

Reputation: 1114

I think there has to be a loop to handle this. Here's SQL Server function that will parse this out. (I know the question didn't specify SQL Server, but it's an example of how it can be done.)

select dbo.ScrubFieldValue(value) from table will return what you're looking for

CREATE FUNCTION ScrubFieldValue
(
    @Input varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @retval varchar(8000)
    DECLARE @charidx int
    DECLARE @remaining varchar(8000)
    DECLARE @current varchar(8000)
    DECLARE @currentLength int

    select @retval = ''
    select @remaining = @Input
    select @charidx = CHARINDEX('CN=', @remaining,2)

    while(LEN(@remaining) > 0)
    BEGIN
        --strip current row from remaining
        if (@charidx > 0)
        BEGIN
            select @current = SUBSTRING(@remaining, 1, @charidx - 1)
        END
        else
        BEGIN
            select @current = @remaining
        END
        select @currentLength = LEN(@current)
        -- get current name
        select @current = SUBSTRING(@current, 4, CHARINDEX(',OU', @current)-4)
        select @retval = @retval + @current + ' '
        -- strip off current from remaining
        select @remaining =substring(@remaining,@currentLength + 1,
            LEN(@remaining) -  @currentLength)
        select @charidx = CHARINDEX('CN=', @remaining,2)
    END
    RETURN @retval
END

Upvotes: 0

mimi
mimi

Reputation: 170

In Oracle 11g, it might work.

REGEXP_SUBSTR(REGEXP_SUBSTR(COLUMN_NAME,  '[^CN=]+',1,1),'[^,OU]+',1,1)

Upvotes: 0

yurexus
yurexus

Reputation: 323

You can try this one SUBSTRING(ColumnName, 4, CHARINDEX(',', ColumnName) - 4)

Upvotes: 2

Related Questions