declare @table table(ad_num varchar(20), ad_str1 varchar(20))

insert @table
select '20', '20 apple avenue' union all
select '20', 'apple avenue'


select * from @table

select ad_str1
	,case
		when substring(ad_str1, 1, 1) like '[0-9]' 
		then 'address starts with a number'
		else 'address starts with a letter'
		end
from	@table

declare @ad_str1 varchar(20)
select top 1 @ad_str1 = ad_str1 from @table

if substring(@ad_str1,1,1) like '[0-9]'
	print @ad_str1 + ': address starts with a number'
else
	print @ad_str1 + ': address starts with a letter'

quote:

---

Originally posted by gongxia649

TG, why doesn't run without storing the field in a variable?

---

quote:

---

Originally posted by gongxia649

Why is this happening? How can i fix it?

---

select @currentvoter = min(id_voter),
       @maxvoter = max(id_voter)
from   tempaddressparsingtable

while @currentvoter <= @maxvoter
  begin
    select @ad = ad_str
    from   tempaddressparsingtable
    where  id_voter = @currentvoter

    if @ad like '[a-z]%'
       exec proc1 @ad
    else
       exec proc2 @ad

    select @currentvoter = min(id_voter)
    from   tempaddressparsingtable
    where  id_voter > @currentvoter
  end

SET NOCOUNT ON

DECLARE @TAPT TABLE (id_voter VARCHAR(2), ad_num VARCHAR(2), ad_str1 VARCHAR(20))

INSERT	@TAPT
SELECT '1', null, '102 OLD COUNTY ROAD' UNION ALL
SELECT '21', null, '1 UNTY ROAD' UNION ALL
SELECT '31', null, 'route 1' UNION ALL
SELECT '14', null, 'pob 23' UNION ALL
SELECT '15', null, 'database apt3' UNION ALL
SELECT '2','30', 'SHAW ROAD apt #9'

SELECT * FROM @TAPT

DECLARE	@CurrentVoter VARCHAR(2),
	@MaxVoter VARCHAR(2),
	@ad VARCHAR(20)

SELECT	@CurrentVoter = MIN(id_voter),
	@MaxVoter = MAX(id_voter)
FROM	@TAPT

WHILE @CurrentVoter <= @MaxVoter
	BEGIN
		SELECT	@ad = ad_str1
		FROM	@TAPT
		WHERE	id_voter = @CurrentVoter

		IF @ad LIKE '[a-z]%'
			--EXEC usp_Trio_AddressParsing1
			PRINT 'Address ' +  @ad + ' does start with a character!'
		ELSE
			--EXEC usp_NDS_AddressParsing1
			PRINT 'Address ' +  @ad + ' does not start with a character!'

		SELECT	@CurrentVoter = MIN(id_voter)
		FROM	@TAPT
		WHERE	id_voter > @CurrentVoter
END

Address 102 OLD COUNTY ROAD does not start with a character!
Address pob 23 does start with a character!
Address database apt3 does start with a character!
Address SHAW ROAD apt #9 does start with a character!
Address 1 UNTY ROAD does not start with a character!
Address route 1 does start with a character!

quote:

---

Originally posted by gongxia649

I thought the SP know how to work with them after the IF statement has ran.

---