so in analyzing the data it appears that the system_id from the client table is what matches to the matter_id table. Thanks for all your help guys.

Thank you. This worked.

Yes, it is OpenText DM system. Formerly known as hummingbird.

TABLE_CATALOGTABLE_SCHEMATABLE_NAMECOLUMN_NAMEORDINAL_POSITIONCOLUMN_DEFAULTIS_NULLABLEDATA_TYPECHARACTER_MAXIMUM_LENGTHCHARACTER_OCTET_LENGTHNUMERIC_PRECISIONNUMERIC_PRECISION_RADIXNUMERIC_SCALEDATETIME_PRECISIONCHARACTER_SET_CATALOGCHARACTER_SET_SCHEMACHARACTER_SET_NAMECOLLATION_CATALOGCOLLATION_SCHEMACOLLATION_NAMEDOMAIN_CATALOGDOMAIN_SCHEMADOMAIN_NAME

DOCS_ALBANYDOCSADMCLIENTSYSTEM_ID1NULLNOintNULLNULL10100NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

DOCS_ALBANYDOCSADMCLIENTCLIENT_ID2NULLYESvarchar1010NULLNULLNULLNULLNULLNULLiso_1NULLNULLSQL_Latin1_General_CP1_CI_ASNULLNULLNULL

DOCS_ALBANYDOCSADMCLIENTCLIENT_NAME3NULLYESvarchar6060NULLNULLNULLNULLNULLNULLiso_1NULLNULLSQL_Latin1_General_CP1_CI_ASNULLNULLNULL

DOCS_ALBANYDOCSADMCLIENTDISABLED4NULLYESvarchar11NULLNULLNULLNULLNULLNULLiso_1NULLNULLSQL_Latin1_General_CP1_CI_ASNULLNULLNULL

DOCS_ALBANYDOCSADMCLIENTTARGET_DOCSRVR5NULLYESintNULLNULL10100NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

DOCS_ALBANYDOCSADMMATTERSYSTEM_ID1NULLNOintNULLNULL10100NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

DOCS_ALBANYDOCSADMMATTERCLIENT_ID2NULLYESintNULLNULL10100NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

DOCS_ALBANYDOCSADMMATTERMATTER_ID3NULLYESvarchar1010NULLNULLNULLNULLNULLNULLiso_1NULLNULLSQL_Latin1_General_CP1_CI_ASNULLNULLNULL

DOCS_ALBANYDOCSADMMATTERMATTER_NAME4NULLYESvarchar6060NULLNULLNULLNULLNULLNULLiso_1NULLNULLSQL_Latin1_General_CP1_CI_ASNULLNULLNULL

DOCS_ALBANYDOCSADMMATTERBILLABLE5NULLYESvarchar11NULLNULLNULLNULLNULLNULLiso_1NULLNULLSQL_Latin1_General_CP1_CI_ASNULLNULLNULL

DOCS_ALBANYDOCSADMMATTERDISABLED6NULLYESvarchar11NULLNULLNULLNULLNULLNULLiso_1NULLNULLSQL_Latin1_General_CP1_CI_ASNULLNULLNULL

DOCS_ALBANYDOCSADMMATTERTARGET_DOCSRVR7NULLYESintNULLNULL10100NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

Yeah, I see that. I took out the and c.client_id not like '%.%'

Still get the error.

Running the following now but same issue.

select c.client_id,

c.client_name,...

the docsadm.matter.client_id field is int

How would I go about converting this in my query so that I get the desired results.

Thank you. That work like a charm. I appreciate the response.

It works if I type in the values I need changing but when I modify your query to run against the accountmain table using the code field it returns me...

thanks. After looking at your query it made sense. I also just read the information on that Function. Thank you.

The string in those fields can start with any Letter not always C or P

Yes, please. I am not familiar with that function.

Thanks. Worked like a Gem.

Thank you very much. Almost too easy, thanks for a second set of eyes on this.

Thank you.

Maybe I should have been more specific or more detailed. Thanks for the reply though.

This is what worked and returned me what I needed.

select code,

(substring(code,

charindex('-',Code)+1 ,

len(Code)))

from accountmain

Data returned...