Error or Data Checking? - 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 or Data Checking?

Author

Topic

george.greiner
Starting Member

19 Posts

Posted - 2009-11-09 : 12:22:12

I have an issue where I am running a query that pulls everything to the left of Chr(13) but am not sure how to deal with it if there is no Chr(13) in the field which does occur sometimes. The code I am using is below:

SELECT  LEFT (defendant_info, CHARINDEX(CHAR(13),defendant_info) -1) AS def, defAdd, RIGHT (defendant_info, 5) AS defZip, tiff = CASE WHEN CHARINDEX(CHAR(13),plaintiff_info) >= 0 THEN LEFT (plaintiff_info, CHARINDEX(CHAR(13),plaintiff_info) -1) Else plaintiff_info END, tiffAdd, Right(plaintiff_info, 5) As tiffZip  INTO copydefendantsallfixed 
                     
FROM         copyofdefendantsAll

Also I have no idea how to pull the address that would be after the first Chr(13) and before the second Chr(13). I come from an Access background and am having trouble converting my Access query to SQL. I am doing this to speed the process up because it is painfully slow running it in Access.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-09 : 12:34:31

replace
LEFT (defendant_info, CHARINDEX(CHAR(13),defendant_info) -1)
with
LEFT (defendant_info, CASE WHEN CHARINDEX(CHAR(13),defendant_info)>0 THEN CHARINDEX(CHAR(13),defendant_info) -1 ELSE LEN(defendant_info) END)

how many char(13) does your string contain? are you trying to parse and get values in between?

george.greiner
Starting Member

19 Posts

Posted - 2009-11-09 : 12:35:02

Error I am getting is as follows:

Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.

george.greiner
Starting Member

19 Posts

Posted - 2009-11-09 : 12:36:41

The data looks as such:

George Greiner Chr(13) Chr(12) 1234 Market Street Chr(13) Chr(12)Philadelphia, PA 19103

I need it to be in 3 fields Name, Add1 and Zip for both defendant_info and plaintiff_info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-09 : 12:40:17

will it be always 3 words separated?

george.greiner
Starting Member

19 Posts

Posted - 2009-11-09 : 12:43:02

quote:
Originally posted by visakh16

will it be always 3 words separated?

Yes there will always be 2 hard returns. Unless you are specifying 3 words within the two returns? That would be no. The data could be in there many ways as the people who we get this data from do not use and Add2 field.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-09 : 12:53:13

ok then easiest way is

if always there are 3 words use


SELECT PARSENAME(REPLACE(yourcol,CHAR(13)+Char(12),'.'),3) AS name,
PARSENAME(REPLACE(yourcol,CHAR(13)+Char(12),'.'),2) AS Add1,
PARSENAME(REPLACE(yourcol,CHAR(13)+Char(12),'.'),1) AS Zip
from...

george.greiner
Starting Member

19 Posts

Posted - 2009-11-09 : 12:56:46

quote:
Originally posted by visakh16

ok then easiest way is

if always there are 3 words use

SELECT PARSENAME(REPLACE(yourcol,CHAR(13)+Char(12),'.'),3) AS name,
PARSENAME(REPLACE(yourcol,CHAR(13)+Char(12),'.'),2) AS Add1,
PARSENAME(REPLACE(yourcol,CHAR(13)+Char(12),'.'),1) AS Zip
from...

There will always be 3 in one field but the other field is a different story and it can range from 1 to 3. How do I pull the middle out of that using the aforementioned code. I ran the line you gave me a few posts above and it worked great but still need to figure out that second field.

Thanks for help it is greatly appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-09 : 12:58:23

sorry didnt get that. how many fields you've in total?

george.greiner
Starting Member

19 Posts

Posted - 2009-11-09 : 13:29:33

quote:
Originally posted by visakh16

sorry didnt get that. how many fields you've in total?

There are 2 fields with the hard returns that need to be separated in the same way but as I said the second one is hit or miss in terms of how many hard returns there happen to be. It could be none, one or two.

george.greiner
Starting Member

19 Posts

Posted - 2009-11-09 : 16:41:04

Another issue that is coming up is that when Rd has a . after it for instance:

George Greiner
1234 Market Rd.
Philadelphia, PA 19103

Add1 comes out NULL instead of 1234 Market Rd.

Any idea what is up with that?

Thanks,

George

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-10 : 12:55:24

quote:
Originally posted by george.greiner

Another issue that is coming up is that when Rd has a . after it for instance:

George Greiner
1234 Market Rd.
Philadelphia, PA 19103

Add1 comes out NULL instead of 1234 Market Rd.

Any idea what is up with that?

Thanks,

George

then what you need is to use the solution as shown in below link

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113563

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources