Converting NVARCHAR to INT after SUBSTRING using CHARINDEX and LEN

I am trying to join two tables using UI’s but the UI in one of the tables has ‘CLIENT_’ before it. When trying to remove the ‘CLIENT_’ from the string and then compare the UI’s I am getting the following error.

Error converting data type nvarchar to float.

When attempting to convert or cast the whole subquery to INT or FLOAT the same error occurs.

Has anyone got any ideas or know how I can code to get this working.

SELECT DISTINCT 69 AS 'FIELD_ID',
ENTITY_ID,
AD.WIP AS 'WIP'
FROM [WORKVIEW].[DBO].ENTITY_MASTER EM
JOIN [LIVE].[MHGROUP].PROJECTS P ON P.PRJ_ID = EM.ENTITY_PARENT 
JOIN [WORKVIEW].[DBO].adn_NAMEACCOUNTSDATA_STAGING AD ON 
CAST((SELECT SUBSTRING(P.CUSTOM1, CHARINDEX ('_', P1.CUSTOM1) -1, LEN(P1.CUSTOM1)) 
FROM [IMAN-SQL].[LIVE].[MHGROUP].PROJECTS P1 
WHERE P.CUSTOM1 LIKE 'CLIENT_%') AS INT) = AD.NAMENO

I would like to be able to join NAMEACCOUNTSDATA_STAGING on P.CUSTOM1 and AD.NAMENO as these are the only fields I can join these tables together with.

LATEST ATTEMPT AT MAKING THIS WORK….

SELECT DISTINCT 69 AS 'FIELD_ID',
ENTITY_ID,
AD.WIP AS 'WIP'
FROM [WORKVIEW].[DBO].ENTITY_MASTER EM
JOIN [IMAN-SQL].[LIVE].[MHGROUP].PROJECTS P ON P.PRJ_ID = EM.ENTITY_PARENT 
JOIN [WORKVIEW].[DBO].WORKVIEW_NAMEACCOUNTSDATA_STAGING AD ON CONCAT('CLIENT_', AD.NAMENO) = P.CUSTOM1 AND P.CUSTOM1 LIKE 'CLIENT_%'

Schema’s for all three tables involved:

TABLE [dbo].[entity_master](
[entity_id] [int] IDENTITY(1,1) NOT NULL,
[entity_parent] [nvarchar](50) NOT NULL,
[entity_child] [nvarchar](50) NOT NULL,
[entity_status] [int] NOT NULL,
[entity_child_name] [nvarchar](250) NULL,
[entity_parent_name] [nvarchar](250) NULL,
[GUID] [varchar](50) NULL,

TABLE [dbo].[WORKVIEW_NAMEACCOUNTSDATA_STAGING](
[NAMENO] [int] NOT NULL,
[WIP] [decimal](18, 2) NULL,
[BILLED] [decimal](18, 2) NULL,
[WRITTENOFF] [decimal](18, 2) NULL,
[DEBTORRESTRICTION] [nvarchar](max) NULL,
[PAYMENTTERMS] [int] NULL,
[BILLING FREQUENCY] [nvarchar](max) NULL

TABLE [MHGROUP].[PROJECTS](
[PRJ_ID] [float] NOT NULL,
[PRJ_PID] [float] NULL,
[DEFAULT_SECURITY] [char](1) NOT NULL,
[IS_SECURED] [char](1) NOT NULL,
[PRJ_NAME] [nvarchar](254) NULL,
[PRJ_OWNER] [nvarchar](64) NULL,
[PRJ_DESCRIPT] [nvarchar](254) NULL,
[PRJ_STATE] [char](1) NULL,
[PRJ_PUBLIC] [char](1) NULL,
[PRJ_LOCATION] [nvarchar](254) NULL,
[TYPE] [int] NOT NULL,
[SUBTYPE] [nvarchar](64) NULL,
[INHERITS_SECURITY] [char](1) NOT NULL,
[DOCNUM] [float] NULL,
[VERSION] [int] NULL,
[CUSTOM1] [nvarchar](254) NULL,
[CUSTOM2] [nvarchar](254) NULL,
[CUSTOM3] [nvarchar](254) NULL,
[LEFT_VISIT] [int] NULL,
[RIGHT_VISIT] [int] NULL,
[TREE_ID] [int] NULL,
[EMAIL] [nvarchar](254) NULL,
[DOC_SAVED_SEARCH] [int] NULL,
[LAYOUT_NAME] [nvarchar](254) NULL,
[LAYOUT_ORDER] [int] NULL,
[LAYOUT_VIEW] [nvarchar](max) NULL,
[REFERENCE_DATABASE] [nvarchar](32) NULL,
[REFERENCE_PRJ_ID] [float] NULL,
[REFERENCE_TYPE] [int] NULL,
[REFERENCE_SUBTYPE] [nvarchar](64) NULL,
[IS_EXTERNAL] [char](1) NOT NULL,
[EXTRNL_AS_NRML] [char](1) NOT NULL,
[IS_DOC_SVD_SRCH] [char](1) NOT NULL,
[IS_PRJ_SVD_SRCH] [char](1) NOT NULL,
[IS_HIDDEN] [char](1) NOT NULL,
[EDITWHEN] [datetime] NOT NULL,

Hope that’s what you were looking for.

Answer

Convert the number to a string!

(SELECT SUBSTRING(P.CUSTOM1, CHARINDEX ('_', P1.CUSTOM1) -1, LEN(P1.CUSTOM1)) 
 FROM [IMAN-SQL].[LIVE].[MHGROUP].PROJECTS P1 
 WHERE P.CUSTOM1 LIKE 'CLIENT_%'
) = CONVERT(NVARCHAR(254), AD.NAMENO)

Or:

(SELECT P.CUSTOM1
 FROM [IMAN-SQL].[LIVE].[MHGROUP].PROJECTS P1 
 WHERE P.CUSTOM1 LIKE 'CLIENT_%'
) = CONCAT('CLIENT_', AD.NAMENO)