CODEWITHSUNDEEP
mysqlsql
user5493185
user5493185

Reputation:

How do I display a part of column Value in sql?

I Need to Extract the Date from Address column Value . It may be possible that my address column value may have street no , house no , city , state , country, day etc

But I want to extract only City value or state value or country value . How can i extract that particular if all values are stored in one address column .

I am using mysql database . My structure of that column is -

Column Name - Address

Column Type - varchar(500)

Common Column value -

Street No-4 , House No-29 , Prabhat Nagar , Ludhiana , Punjab , India , wednessday , 25 may 2014

There is check available in Code To Check the required values are not empty. Streeno , house no , city , country ,state are required .If any of required fields is empty column will will not be inserted or updated in db and will empty

Code For Date Format is also fixed in code and it will go in this format everytime

Upvotes: 1

Views: 4069

Answers (3)

Utsav
Utsav

Reputation: 8093

I created this. It will separate all your values. You forgot to mention the Area so I added it also.

SQLFiddle Demo

select 
    substring_index(substring_index(Address, ',', 1),',',-1) as street_no,
    substring_index(substring_index(Address, ',', 2),',',-1) as house_no,
    substring_index(substring_index(Address, ',', 3),',',-1) as area,
    substring_index(substring_index(Address, ',', 4),',',-1) as city,
    substring_index(substring_index(Address, ',', 5),',',-1) as state,
    substring_index(substring_index(Address, ',', 6),',',-1) as country,
    substring_index(substring_index(Address, ',', 7),',',-1) as day,
    STR_TO_DATE(trim(substring_index(substring_index(Address, ',', 8),',',-1)),'%d %b %Y') as date1
from table1

Output

+-------------+-------------+---------------+----------+--------+---------+------------+-----------------------+
|  street_no  |  house_no   |     area      |   city   | state  | country |    day     |         date1         |
+-------------+-------------+---------------+----------+--------+---------+------------+-----------------------+
| Street No-4 | House No-29 | Prabhat Nagar | Ludhiana | Punjab | India   | wednessday | May, 25 2014 00:00:00 |
+-------------+-------------+---------------+----------+--------+---------+------------+-----------------------+

Upvotes: 0

Heemanshu Bhalla
Heemanshu Bhalla

Reputation: 3765

You Need to make some changes that it will work good .

So While entering data in Address Column you need to maintain a structure Everytime you insert data maintain that structure then you can select your desired result with query below -

SELECT Mid(Title, Instr(Title, 'Day') + 4, Instr(Title, '|')- Instr(Title, ':')-1) As LastName FROM tblblog

My Address Column Value in Database is as below

Address

House No:29| Street No:4| Country:India| City:Ludhiana| Day:Thursday|

In that way you can search for country , city as well and it works for other values also .

Explanation :

The Instr function

It is often used in combination with other string functions for manipulating string values. The Instr function returns the position of a string occurring within another string. The format of the function is as follows:

Instr ( [start], stringToSearch, stringToFind)

The start parameter is optional and specifies where in the string we will start searching for the second string. If we wanted to start at the fifth character of the first string then we would specify 5 for the start parameter. If this is left blank then the search is started at the beginning of the first string.

The Mid Function

The Mid(string, start, length) function returns a portion of a string starting from a specified position and containing a specified number of characters.

The following extracts the month value from a string column in the format dd/mm/yy. The extracted string starts at the 4th character in the string and ends after 2 characters.

Upvotes: 1

Muhammad Muazzam
Muhammad Muazzam

Reputation: 2800

Ensure the city name is starting with comma(,):

      SELECT
      LEFT(Address, CHARINDEX(',', Address)-1) as city,
      SUBSTRING(Address, CHARINDEX(',', Address)+1,
      LEN(Address) - CHARINDEX(' ', REVERSE(Address)) - CHARINDEX(',', Address)
      )  as state,
      RIGHT(Address, CHARINDEX(' ', REVERSE(Address)) - 1) as zip

It gets the city as the beginning of the string to the comma.
The zip is the end of the string going backwards until if finds the first space, so a zip of 19702 or 19702-1234 will work. The state is anything between the comma and last space in the string.

Upvotes: 0

Related Questions