--create function to pull number value --insert value into temp table --insert value of 4 or greater back into response table (currently a select) --drop function --drop temp table

SpeakerLeader.sql

use mip_portal
go

CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO

select dbo.udf_GetNumeric(r.response_answer) as responseNum, r.*
into #responseTemp
from response r
join survey_question sq on sq.survey_question_id = r.survey_question_id
join question q on q.question_id = sq.question_id
join question_group qg on qg.question_group_id = q.question_group_id
where sq.survey_id = 82
and qg.question_group_comp_id = 10

--insert into response
select distinct null, provider_id, provider_survey_id, MAX(responseNum), null, GETDATE(), 'nghia.tran', GETDATE(), 'nghia.tran', 10170, null
from #responseTemp
where responsenum > 3
group by org_site_id, provider_id, provider_survey_id

/*
drop function dbo.udf_GetNumeric
drop table #responseTemp
*/