SQL Basics

 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Tutorialized ForumsDatabasesSQL Basics

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Tutorialized Forums Sponsor:
  #1  
Old November 16th, 2012, 05:11 PM
kshahborr1 kshahborr1 is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Sep 2012
Posts: 3 kshahborr1 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 31 m 39 sec
Reputation Power: 0
Join with a case statement

Hi,

I have 2 tables. One table has a column called State and Zipcode which has entries like

Sate Zipcode
Chicago 001122:1123
Indianapolis 776635:2356
Los Angeles 8877678:7689, 9900879:0987
San Francisco 776543:9909, 443344:1111, 998877

I have another table which has 2 columns

NameAlias FullName

001122 rockford
776635 westfield
8877678 Lake Tahoe
9900879 Hollywood
776543 Crucked Lane
443344 Tram Lake
998877 Fancis Lane


I would like to join this 2 tables so I see :


Chicago 001122 rockford
Indianapolis 776635 westfield
Los Angeles 8877678 Lake Tahoe
Los Angeles 9900879 Hollywood
San Francisco 776543 Crucked Lane
San Francisco 443344 Tram Lake
San Francisco 998877 Fancis Lane

Any pointers you can give pls.

Regards

Raj-

Reply With Quote
  #2  
Old November 17th, 2012, 02:10 AM
dibyajyotibeher dibyajyotibeher is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Nov 2012
Posts: 3 dibyajyotibeher User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 36 m 34 sec
Reputation Power: 0
you have to split the Zipcode column on the basis of "," and ":" . There's no inbuilt function in mysql although you can write on .. Check
Or better still you can extract the string and process it in perl or ruby easily





Quote:
Originally Posted by kshahborr1
Hi,

I have 2 tables. One table has a column called State and Zipcode which has entries like

Sate Zipcode
Chicago 001122:1123
Indianapolis 776635:2356
Los Angeles 8877678:7689, 9900879:0987
San Francisco 776543:9909, 443344:1111, 998877

I have another table which has 2 columns

NameAlias FullName

001122 rockford
776635 westfield
8877678 Lake Tahoe
9900879 Hollywood
776543 Crucked Lane
443344 Tram Lake
998877 Fancis Lane


I would like to join this 2 tables so I see :


Chicago 001122 rockford
Indianapolis 776635 westfield
Los Angeles 8877678 Lake Tahoe
Los Angeles 9900879 Hollywood
San Francisco 776543 Crucked Lane
San Francisco 443344 Tram Lake
San Francisco 998877 Fancis Lane

Any pointers you can give pls.

Regards

Raj-

Reply With Quote
  #3  
Old November 17th, 2012, 07:48 PM
kshahborr1 kshahborr1 is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Sep 2012
Posts: 3 kshahborr1 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 31 m 39 sec
Reputation Power: 0
[QUOTE=dibyajyotibeher]

Thank you. Would you have any sample code in sql you could share which I can refer to attain this.

Reply With Quote
  #4  
Old November 18th, 2012, 01:22 AM
dibyajyotibeher dibyajyotibeher is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Nov 2012
Posts: 3 dibyajyotibeher User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 36 m 34 sec
Reputation Power: 0
Quote:
Originally Posted by kshahborr1
[QUOTE=dibyajyotibeher]

Thank you. Would you have any sample code in sql you could share which I can refer to attain this.


Forum rules are preventing me from posting the url. .. so posting the page here ---
MySQL does not include a function to split a delimited string. However, itís very easy to create your own function.

Create function syntax

A user-defined function is a way to extend MySQL with a new function that works like a native MySQL function.

CREATE [AGGREGATE] FUNCTION function_name
RETURNS {STRING|INTEGER|REAL|DECIMAL}
To create a function, you must have the INSERT privilege for the <mysql> database.

Split delimited strings

The following example function takes 3 parameters, performs an operation using an SQL function, and returns the result.

Function

CREATE FUNCTION SPLIT_STR(
x VARCHAR(255),
delim VARCHAR(12),
pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
delim, '');
Usage

SELECT SPLIT_STR(string, delimiter, position)
Example

SELECT SPLIT_STR('a|bb|ccc|dd', '|', 3) as third;

+-------+
| third |
+-------+
| ccc |
+-------+

Reply With Quote
Reply

Viewing: Tutorialized ForumsDatabasesSQL Basics > Join with a case statement


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap