error then insert null - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

error then insert null

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2012-10-25 : 10:49:03

Hi All,

I have the following query, I want to insert NULL to the rows when the col1 length is of inappropriate length or resume to the next row. Is it possible to do that.



select	col1,
	case 	when col1 like '%AB%'
		then substring(col1, charindex('AB', col1) + 3, charindex(' ', col1 + ' ', charindex('AB', col1)) - charindex('AB', col1) - 3)
		end,
	case 	when col1 like '%CD%'
		then substring(col1, charindex('CD', col1) + 3, charindex(' ', col1 + ' ', charindex('CD', col1)) - charindex('CD', col1) - 3)
		end,
	case 	when col1 like '%EF%'
		then substring(col1, charindex('EF', col1) + 3, charindex(' ', col1 + ' ', charindex('EF', col1)) - charindex('EF', col1) - 3)
		end
from	tbl

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-25 : 10:52:39

What's an inappropriate length?

select col1,
case when len(col1) - charindex('AB', col1) > 10
then substring(col1, charindex('AB', col1) + 3, charindex(' ', col1 + ' ', charindex('AB', col1)) - charindex('AB', col1) - 3)
end,
case when len(col1) - charindex('CD', col1) > 10
then substring(col1, charindex('CD', col1) + 3, charindex(' ', col1 + ' ', charindex('CD', col1)) - charindex('CD', col1) - 3)
end,
case when len(col1) - charindex('EF', col1) > 10
then substring(col1, charindex('EF', col1) + 3, charindex(' ', col1 + ' ', charindex('EF', col1)) - charindex('EF', col1) - 3)
end
from tbl

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 11:14:05

quote:

Originally posted by anjali5

Hi All,

I have the following query, I want to insert NULL to the rows when the col1 length is of inappropriate length or resume to the next row. Is it possible to do that.



select	col1,
	case 	when col1 like '%AB%'
		then substring(col1, charindex('AB', col1) + 3, charindex(' ', col1 + ' ', charindex('AB', col1)) - charindex('AB', col1) - 3)
		end,
	case 	when col1 like '%CD%'
		then substring(col1, charindex('CD', col1) + 3, charindex(' ', col1 + ' ', charindex('CD', col1)) - charindex('CD', col1) - 3)
		end,
	case 	when col1 like '%EF%'
		then substring(col1, charindex('EF', col1) + 3, charindex(' ', col1 + ' ', charindex('EF', col1)) - charindex('EF', col1) - 3)
		end
from	tbl

NULL for what all fields?

what do you mean by or resume to the next row

do you mean ignoring them altogether?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anjali5
Posting Yak Master

121 Posts

Posted - 2012-10-25 : 12:45:39

NULL for all fields or ignoring them all together so if the string is of length 2 or even the length is right, but it is not in the format as expected then I want NULL in all fields or ignore that particular row and go to the next one.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 15:03:25

quote:
Originally posted by anjali5

NULL for all fields or ignoring them all together so if the string is of length 2 or even the length is right, but it is not in the format as expected then I want NULL in all fields or ignore that particular row and go to the next one.

ok...
in that case you just need to apply a filter based on LEN() function

didnt understand whats the deal with format. do you've specific format that you're looking at?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources