Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
gongxia649
So Suave
344 Posts |
Posted - 2006-09-20 : 17:03:06
|
declare @table table(ad_num varchar(20), ad_str1 varchar(20))insert @tableselect '20', '20 apple avenue' union allselect '20', 'apple avenue'select * from @tableif (select * from @table where substring(ad_str1, 1, 1) like '[0-9]'))print 'address starts with a number'elseprint 'address starts with a letter'i want to write a code that prints a msg if the string starts with a number else print another msg. |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-09-20 : 17:16:25
|
Looks like you're confusing IF with CASE. Check these out:declare @table table(ad_num varchar(20), ad_str1 varchar(20))insert @tableselect '20', '20 apple avenue' union allselect '20', 'apple avenue'select * from @tableselect ad_str1 ,case when substring(ad_str1, 1, 1) like '[0-9]' then 'address starts with a number' else 'address starts with a letter' endfrom @tabledeclare @ad_str1 varchar(20)select top 1 @ad_str1 = ad_str1 from @tableif 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' Be One with the OptimizerTG |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-09-20 : 17:20:57
|
Not much point in using SubString, in fact it's less optimal than:select ad_str1 ,case when ad_str1 like '[0-9]%' then 'address starts with a number' else 'address starts with a letter' endfrom @tableThis is semantically equivalent and it avoids the non-sargable SubString function, on a large table with an indexed column it should be dramatically faster. |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-09-20 : 21:03:54
|
TG, why doesn't run without storing the field in a variable? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-09-20 : 22:12:38
|
quote: Originally posted by gongxia649 TG, why doesn't run without storing the field in a variable?
IF expects to evaluate a boolean expression (true or false). Your "exression":if (select * from @table where substring(ad_str1, 1, 1) like '[0-9]'))does not result in a boolean expression but rather a result set. Also, you've got an extra parenthises at the end.Be One with the OptimizerTG |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-09-21 : 16:42:15
|
truncate table dbo.TempAddressParsingTableinsert dbo.TempAddressParsingTable (id_voter,ad_num,ad_str1)select '1', null, '102 OLD COUNTY ROAD' union allselect '21', null, '1 UNTY ROAD' union allselect '31', null, 'route 1' union allselect '14', null, 'pob 23' union allselect '15', null, 'database apt3' union allselect '2','30', 'SHAW ROAD apt #9'-- select * from dbo.TempAddressParsingTable where substring(ad_str1,1,1) like '[a-z]'-- select * from dbo.TempAddressParsingTable where substring(ad_str1,1,1) like '[0-9]'select * from dbo.TempAddressParsingTable declare @ad_str1 varchar(20)select @ad_str1 = ad_str1 from dbo.TempAddressParsingTableif substring(@ad_str1,1,1) like '[a-z]'exec proc1else exec proc2The code should execute proc1 if the field ad_str1 first letter is a letter, otherwise exec proc2.The code runs fine, but it's not doing what the proc are suppose to do. But if i run the SP alone, it will do what is suppose to do. Why is this happening? How can i fix it? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-21 : 16:49:37
|
quote: Originally posted by gongxia649 Why is this happening? How can i fix it?
You could start learning basic algorithms.select @currentvoter = min(id_voter), @maxvoter = max(id_voter)from tempaddressparsingtablewhile @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 Peter LarssonHelsingborg, Sweden |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-09-22 : 08:21:57
|
truncate table dbo.TempAddressParsingTableinsert dbo.TempAddressParsingTable (id_voter,ad_num,ad_str1)select '1', null, '102 OLD COUNTY ROAD' union allselect '21', null, '1 UNTY ROAD' union allselect '31', null, 'route 1' union allselect '14', null, 'pob 23' union allselect '15', null, 'database apt3' union allselect '2','30', 'SHAW ROAD apt #9'-- select * from dbo.TempAddressParsingTable where substring(ad_str1,1,1) like '[a-z]'-- select * from dbo.TempAddressParsingTable where substring(ad_str1,1,1) like '[0-9]'select * from dbo.TempAddressParsingTable declare @currentvoter varchar(20)declare @maxvoter varchar(20)declare @ad varchar(20)select @currentvoter = min(id_voter) from tempaddressparsingtableselect @maxvoter = max(id_voter)from tempaddressparsingtablewhile @currentvoter <= @maxvoter begin select @ad = substring(ad_str1, 1,1) from dbo.tempAddressParsingtable where id_voter = @currentvoter if substring(@ad, 1,1) like '[a-z]%' exec usp_Trio_AddressParsing1 else exec usp_NDS_AddressParsing1 select @currentvoter = min(id_voter) from dbo.tempaddressparsingtable where id_voter > @currentvoter endI don't know what i'm doing wrong. When i run the code, the SP are not doing the job. But if I exec them alone, they work fine. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-22 : 08:37:20
|
Why, Oh Why, are you changing the code and logic in the suggestions?And you are totally clueless why the code doesn't work after your changes? I am a peaceful person, but you are starting to make me dislike you.Why do you use SUBSTRING twice? Do you not trust us who tries to help you?If the stored procedures are not "doing the job", have you thought of the possibility that my suggestion is right and your stored procedures has errors when executed?Always, when debugging, replace code to calling other stored procedures with a PRINT statement, like the one below. This way you will be convinced that my suggestion is good enough and the appropriate stored procedure is called.Also in my first suggestion, I called the different stored procedures with a parameter, the address. Now when you have deleted them, how will the two procedures know what to work with?SET NOCOUNT ONDECLARE @TAPT TABLE (id_voter VARCHAR(2), ad_num VARCHAR(2), ad_str1 VARCHAR(20))INSERT @TAPTSELECT '1', null, '102 OLD COUNTY ROAD' UNION ALLSELECT '21', null, '1 UNTY ROAD' UNION ALLSELECT '31', null, 'route 1' UNION ALLSELECT '14', null, 'pob 23' UNION ALLSELECT '15', null, 'database apt3' UNION ALLSELECT '2','30', 'SHAW ROAD apt #9'SELECT * FROM @TAPTDECLARE @CurrentVoter VARCHAR(2), @MaxVoter VARCHAR(2), @ad VARCHAR(20)SELECT @CurrentVoter = MIN(id_voter), @MaxVoter = MAX(id_voter)FROM @TAPTWHILE @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 > @CurrentVoterEND Peter LarssonHelsingborg, SwedenEDIT: Spelling errors |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-22 : 08:46:59
|
Output from the code above gives followingAddress 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! As you can the, the result is correct. Where is the error, you think?Peter LarssonHelsingborg, Sweden |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-09-22 : 08:57:05
|
peso, i didnt see the parameter next to the SP. I have to do some changes now. But my SP doesnt receive any parameter.Also in my first suggestion, I called the different stored procedures with a parameter, the address. Now when you have deleted them, how will the two procedures know what to work with?I thought the SP know how to work with them after the IF statement has ran. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-22 : 09:06:32
|
quote: Originally posted by gongxia649 I thought the SP know how to work with them after the IF statement has ran.
I am aware you thought so and didn't know, that's why I provided a suggestion for a correct execution to you.No, the stored procedure does not know which address to work with, if you do not provide the address to the stored procedure.Peter LarssonHelsingborg, Sweden |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-09-22 : 09:27:23
|
Peso, "No, the stored procedure does not know which address to work with, if you do not provide the address to the stored procedure."Does that mean I have to go to my SP and change the whole thing? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-22 : 09:27:44
|
How would I know? I don't have a crystal ball.It all depends on what your stored procedures does!If the case is that your stored procedure usp_Trio_AddressParsing1 already knows how to deal with addresses that starts with characters (all addresses in the table, not just one row) then you might add a WHERE clause in the SP, aswhere address like '[a-z]%'If the case is that your stored procedure usp_NDS_AddressParsing1 already knows how to deal with addresses (all addresses in the table, not just one row) that start with a non-character, then you might add a WHERE cluse in the SP, aswhere address like '[^a-z]%'orwhere address like '[0-9]%'Then all you need is two calls to either SP, and this topic is obselete to start with.We can't tell you what to do. We give valuable suggestions. Our suggestions are based on the information you provide us.Not once in this entire topic have you told us what the two stores procedures do. So we think you know what you are doing with the loop and all, as in the original posting.Maybe there were no need for a loop at all!? It all depends on what your stored procedures does!Peter LarssonHelsingborg, Sweden |
|
|
X002548
Not Just a Number
15586 Posts |
|
gongxia649
So Suave
344 Posts |
Posted - 2006-09-22 : 09:48:24
|
usp_Trio_AddressParsing1 = parse the address that no number in frontusp_NDS_AddressParsing1 = parse the address that has numbers in fronti added "where address like '[0-9]%'" and "where address like '[a-z]%'" to all the rows in my sp. But still doesnt' do that job.If i run the SP alone. e.g. i run only usp_Trio_AddressParsing1, it will parse the address |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-22 : 09:50:32
|
the addressorthe addressesPeter LarssonHelsingborg, Sweden |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-09-22 : 09:55:29
|
peso, addressesyou know what, i think its working.I dont even neeed to use IF cause the SP knows when to parse the addresses with number in front and without a number in front.so, i just gotruncate table dbo.TempAddressParsingTableinsert dbo.TempAddressParsingTable (id_voter,ad_num,ad_str1)select '1', null, '102 OLD COUNTY ROAD' union allselect '21', null, '1 UNTY ROAD' union allselect '31', null, 'route 1' union allselect '14', null, 'pob 23' union allselect '15', null, 'database apt3' union allselect '2','30', 'SHAW ROAD apt #9'-- select * from dbo.TempAddressParsingTable where substring(ad_str1,1,1) like '[a-z]'-- select * from dbo.TempAddressParsingTable where substring(ad_str1,1,1) like '[0-9]'select * from dbo.TempAddressParsingTable usp_Trio_AddressParsing1 usp_NDS_AddressParsing1 and bang it works. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-22 : 09:57:41
|
I know.Peter LarssonHelsingborg, Sweden |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-09-22 : 09:59:14
|
sorry agian to wasted your time. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-22 : 10:00:29
|
Well, it might not be a wate of time, if you have improved you T-SQL skills and learned something from this mistakes and never do the same mistake again.Peter LarssonHelsingborg, Sweden |
|
|
Next Page
|
|
|
|
|