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_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_PRECISION_RADIX NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_CATALOG CHARACTER_SET_SCHEMA CHARACTER_SET_NAME COLLATION_CATALOG COLLATION_SCHEMA COLLATION_NAME DOMAIN_CATALOG DOMAIN_SCHEMA DOMAIN_NAME

DOCS_ALBANY DOCSADM CLIENT SYSTEM_ID 1 NULL NO int NULL NULL 10 10 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

DOCS_ALBANY DOCSADM CLIENT CLIENT_ID 2 NULL YES varchar 10 10 NULL NULL NULL NULL NULL NULL iso_1 NULL NULL SQL_Latin1_General_CP1_CI_AS NULL NULL NULL

DOCS_ALBANY DOCSADM CLIENT CLIENT_NAME 3 NULL YES varchar 60 60 NULL NULL NULL NULL NULL NULL iso_1 NULL NULL SQL_Latin1_General_CP1_CI_AS NULL NULL NULL

DOCS_ALBANY DOCSADM CLIENT DISABLED 4 NULL YES varchar 1 1 NULL NULL NULL NULL NULL NULL iso_1 NULL NULL SQL_Latin1_General_CP1_CI_AS NULL NULL NULL

DOCS_ALBANY DOCSADM CLIENT TARGET_DOCSRVR 5 NULL YES int NULL NULL 10 10 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

DOCS_ALBANY DOCSADM MATTER SYSTEM_ID 1 NULL NO int NULL NULL 10 10 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

DOCS_ALBANY DOCSADM MATTER CLIENT_ID 2 NULL YES int NULL NULL 10 10 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

DOCS_ALBANY DOCSADM MATTER MATTER_ID 3 NULL YES varchar 10 10 NULL NULL NULL NULL NULL NULL iso_1 NULL NULL SQL_Latin1_General_CP1_CI_AS NULL NULL NULL

DOCS_ALBANY DOCSADM MATTER MATTER_NAME 4 NULL YES varchar 60 60 NULL NULL NULL NULL NULL NULL iso_1 NULL NULL SQL_Latin1_General_CP1_CI_AS NULL NULL NULL

DOCS_ALBANY DOCSADM MATTER BILLABLE 5 NULL YES varchar 1 1 NULL NULL NULL NULL NULL NULL iso_1 NULL NULL SQL_Latin1_General_CP1_CI_AS NULL NULL NULL

DOCS_ALBANY DOCSADM MATTER DISABLED 6 NULL YES varchar 1 1 NULL NULL NULL NULL NULL NULL iso_1 NULL NULL SQL_Latin1_General_CP1_CI_AS NULL NULL NULL

DOCS_ALBANY DOCSADM MATTER TARGET_DOCSRVR 7 NULL YES int NULL NULL 10 10 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

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...