 |
Product Search
|
 |
|
Article Search
|
|
|
Resources
| |
|
Home > $270.00 To $809.99
We Have Found 3 Products for your search of $270.00 To $809.99.
Displaying Items 1 - 3:
String Manipulation in Microsoft SQL - SUBSTRING
by Shannon Lowder
Before I go into how to go altering strings in SQL, I would like to point out that you have to be careful when and how often you use the techniques below. A good rule to keep in mind is text manipulation is slow and painful to a server. If you can leave the text manipulation to your middle-ware or front end, that would be better. But we all have been stuck in a situation where we needed to alter a string before those two points, and so I bring you the following lesson.
The Problemphone605-555-2862(561)555-2700904-555-5680N/A580-555-53712815558368(254)555-8430336-555-27973365557233592-555-3181/495196615551222 Ext. 249+44 7930 555271
Let's say you have a column in a database that collects phone numbers from a web form and that form doesn't have any JavaScript parsing the code into the three standard parts of a phone number (area code, prefix, and telroot). This data is being dumped into a leads file and the manager from the sales department comes down and asks you to help split up the leads by area code.
The first thing you need to do in a situation like this is become aware of what kind of data you're looking at. Select out the first 100 or so rows and get an idea of what shape the data is in. In this case you can see there are several types of phone numbers entered.
I've limited the example above to just a few of the types of numbers you might find if you allowed users free form access to storing their phone numbers.
3,3,4 Parenthesis, dashes, and 3,3,4 Blank, null, or N/A Parenthesis, dashes, spaces, and 3,3,4 10 numbers Extensions International numbers
The Solution
So how can you deal with all these situations? Use rel=nofollow [http://toyboxcreations.net/2004/08/string-manipulation-substring/]SUBSTRING.
SUBSTRING (string, startPosition, length)Using this function you can check out the first three characters of each phone number.
SELECT TOP 100 phone, SUBSTRING(phone, 1, 3) AS [area code]FROM sourceTable (READCOMMITED)
When looking at the results, you will see that this only shows you the first three characters of the phone number. While the results appear to be accurate in some cases, you should immediately see the exception cases that this doesn't handle. This solution cannot handle cases with parenthesis, international numbers, NULL, blank, or invalid numbers.
To proceed on this problem you'll need a few more tools in your SQL tool belt. Check out other articles I've published on string manipulation. There you will discover how [http://toyboxcreations.net/2005/02/string-manipulation-replace/]REPLACE, LTRIM, RTRIM, and more techniques for manipulating text strings can help solve this problem.
Shannon Lowder
About the Author
To proceed on this problem you'll need a few more tools in your SQL tool belt. Check out other articles I've published on string manipulation. There you will discover how REPLACE, LTRIM, RTRIM, and more techniques for manipulating text strings can help solve this problem.Shannon Lowder
Database Engineer
808 State - Pacific State
Seminal Chillout anthem from 808 State (AUDIO ONLY), released on ZTT in 1989. HIGH QUALITY AUDIO!! Not sure which mix this is though??
***As leorusty correctly pointed out, the UK single release was infact 13 November (and not 6 November - as in the video) of 1989.***
***If there are other classic 12" dance records you would like to suggest I upload then just let me know and I'll see what I can do. Infiltrate202***
1980s Bury Manchester Graham Massey A Guy Called Gerald Simpson Voodoo Ray Rham On Acid Remix Martin Price Darren Partington Andrew Barker Roland TR-808 Drum Machine Percussion TB-303 Mellow Birds Mega Edit 909 707 Cobra Bora Tropical Paradise Island Hawaii Gorgeous Ex:El Ninety 90 LP In Yer Face Cubik Olympic Dream 808:98 12" Balaeric Chill Out Cafe Del Mar Sexy Sax Saxophone
|
|
