Help with the query again. - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with the query again.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2009-01-15 : 13:06:33

Hi,

Many thanks to you for taking the time to help me with construct the query.
The error below occurs because there is some blank '' in Addr3 field (garbage data).
Also the query below still not working because if i insert correct city name then it
truncate the last 2 letters. How can I correct that. Please see the output below.
Again, Thanks in advance.
Error:
.Net SqlClient Data Provider: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

IF OBJECT_ID('Tempdb.dbo.##Staging', 'u') IS NOT NULL
DROP TABLE ##Staging
GO
CREATE TABLE ##Staging
(
Idx INT NULL,
Addr3 CHAR(20) NULL
)
go

insert ##Staging values (18614, 'SN BERNRDNO CA')
insert ##Staging values (33031, 'VICTORVILLE CA')
insert ##Staging values (18687, 'SAN DIEGO CA')
insert ##Staging values (96681, 'SAN DIEGO CA')
insert ##Staging values (68994, 'CARMICHAEL CA')
insert ##Staging values (163692,'SN BERNRDNO CA')
insert ##Staging values (178462, 'ESCONDIDO CA')
insert ##Staging values (189431, 'CHULA VISTA, CA')
insert ##Staging values (6846, 'BANNING , CA')
insert ##Staging values (11318, 'EL CAJON ,CA ')
insert ##Staging values (33032, ' ')
insert ##Staging values (73660, 'LAKE ELSINORE')
insert ##Staging values (89423, ' MORENO VALLEY CA')
go

SELECT *
FROM ##Staging
Go

Idx Addr3
----------- --------------------
18614 SN BERNRDNO CA
33031 VICTORVILLE CA
18687 SAN DIEGO CA
96681 SAN DIEGO CA
68994 CARMICHAEL CA
163692 SN BERNRDNO CA
178462 ESCONDIDO CA
189431 CHULA VISTA, CA
6846 BANNING , CA
11318 EL CAJON ,CA
33032
73660 LAKE ELSINORE
89423 MORENO VALLEY CA

-- Testing, not working...
SELECT idx, Addr3, SUBSTRING(Addr3, 1, (LEN(Addr3)- 2) )
FROM ##Staging
WHERE ((LEN(Addr3) - 2) > 0)
--ORDER BY idx ASC
go

SELECT idx,
Addr3,
rtrim(ltrim(substring(rtrim(ltrim(replace(Addr3, ',',''))),1,(len(rtrim(ltrim(replace(Addr3, ',',''))))-2)))) AS 'Newcityname'
FROM ##Staging
WHERE (LEN(Addr3) - 2 > 0)
go

SELECT idx, Addr3,
REPLACE(SUBSTRING(Addr3, 1,
CASE WHEN LEN(Addr3)- 2 < 0 THEN 0
ELSE (LEN(Addr3)- 2)
END), ',', '')
FROM ##Staging;

It should return:

idx NewAddr3
----------- ---------------
73660 LAKE ELSINORE ** truncate the last 2 letters.

-- Rule: return only city name.
-- Desire result.
idx NewAddr3
----------- ---------------
18614 SN BERNRDNO
33031 VICTORVILLE
18687 SAN DIEGO
96681 SAN DIEGO
68994 CARMICHAEL
163692 SN BERNRDNO
178462 ESCONDIDO
189431 CHULA VISTA
6846 BANNING
11318 EL CAJON
73660 LAKE ELSINORE ** truncate the last 2 letters.
73660 MORENO VALLEY

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-15 : 13:27:45

The query I wrote for you assumes that all data is in the same format (ex: PHOENIX, AZ). It removes the comma if it exists and then truncates the last 2 letters for the state. If you insert data without the 2-letter state abbreviation there will be no way for it to recognize that so it'll chop off the last 2 characters from the city. Your solution should be either inserting all data with the state or cleaning your table to get rid of all state abbreviations and then inserting only city names.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-15 : 13:42:26

[code]select idx,
Addr3,replace(replace(Addr3,',',''),' CA','') from ##Staging[/code]

NguyenL71
Posting Yak Master

228 Posts

Posted - 2009-01-15 : 14:39:00

quote:
Originally posted by sakets_2000

select idx,
Addr3,replace(replace(Addr3,',',''),' CA','') from ##Staging

Thank you for your help but it replace everything with CA and the results are not quite correct. Here is the problem.

select idx,Addr3,
CAST(replace(replace(Addr3,',',''),' CA','') AS VARCHAR(25)) AS 'NewCity'
from ##Staging

idx Addr3 NewCity
----------- -------------------- -------------------------
11318 EL CAJON ,CA ELJON -- it should be EL CAJON

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-01-16 : 00:58:39

Hi Dear,

I hope the below query will help you to fix the issue.

Code Snippet:

SELECT IDX,replace(LEFT(ADDR3,LEN(ADDR3)-2),',','') FROM ##STAGING
WHERE LEN(ADDR3)>3

Use the Above Where Condition or the below condition.

WHERE ADDR3 LIKE '% CA'

Regards
Thiyagarajan
www.sqlhunt.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources