0

I have a particular column that needed a cleaning called Owner Address with a table name NashvilleHousing. Below is the example data of the column;

OwnerAddress
-------------
2935  LOUISE DR, NASHVILLE, TN
3019  LOUISE DR, NASHVILLE, TN
3019  LOUISE DR, NASHVILLE, TN

I want to extract NASHVILLE only for every record. My query is as such;

SELECT
SUBSTRING(OwnerAddress, CHARINDEX(',', OwnerAddress)+1, Len(OwnerAddress) - 4)
FROM NashvilleHousing

Where did I go wrong here? Thank you.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Sofiyah Rahmat
  • 3
  • 2
  • Is Nashville (or more specifically the town) *always* the 2nd delimited item? – Thom A Apr 25 '22 at 14:07
  • Does this answer your question? [Using T-SQL, return nth delimited element from a string](https://stackoverflow.com/questions/19449492/using-t-sql-return-nth-delimited-element-from-a-string) – Thom A Apr 25 '22 at 14:07
  • No Larnu, I have recorded OwnerAddress like this as well; 321 MORTON AVE, NASHVILLE For this case I extract NASHVILLE using this query, SELECT SUBSTRING(OwnerAddress, CHARINDEX(',', OwnerAddress)+1, LEN(OwnerAddress)) FROM NashvilleHousing WHERE OwnerAddress NOT LIKE '%TN' – Sofiyah Rahmat Apr 25 '22 at 14:21
  • `CASE WHEN OwnerAddress LIKE '%NASHVILLE%' THEN 'NASHVILLE' END`? You are not being clear here. – Thom A Apr 25 '22 at 14:22
  • Oh yes it works. But I have cities beside NASHVILLE as well in my record such as 112 VINE LN, GOODLETTSVILLE, TN. I need to extract GOODLETTSVILLE city too. Sorry for not being particular. – Sofiyah Rahmat Apr 25 '22 at 14:38
  • So back to my original question, *"Is Nashville (or more specifically the town) always the 2nd delimited item?"*... – Thom A Apr 25 '22 at 14:41
  • Yes, the town is always the 2nd delimited item. – Sofiyah Rahmat Apr 25 '22 at 14:53
  • I hope that part of your exercise here is to fix this design. You have violated 1NF by storing multiple values in a single tuple. – Sean Lange Apr 25 '22 at 15:32

0 Answers0