<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
T-SQL (SS2K8)
»
Multipe letters into one
41 posts, Page 1 of 5
1
2
3
4
5
»
»»
Multipe letters into one
Rate Topic
Display Mode
Topic Options
Author
Message
karthik M
karthik M
Posted Monday, October 01, 2012 11:21 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 2:56 AM
Points: 2,008,
Visits: 2,469
All,
I have read and asked lot of questionss about "Multiple space into one" here. I just thought the below scenario which came suddenly in my mind.
Incase if the user entered wrongly or if the file contain wrong data as below
kaarthi
jefff moden
steevve
gillla
barrry youuung
mattt milller
Joeee celkko
lowweel
maaark
biiit buucket
graant fritchey
phhhill factor
ChriiisM
We all know their names. :)
Expected Output:
karthi
jef moden
steve
gila
rbary young
mat miler
Joe celko
lowel
mark
bit bucket
grant fritchey
phil factor
ChrisM
For no, I just removed all the multiple letters into one. But this may also cause some issue if some one has really two letters in their name.
I think we have to handle this one too.
karthik
Post #1366675
Jeff Moden
Jeff Moden
Posted Monday, October 01, 2012 11:39 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 32,906,
Visits: 26,793
Actually, I don't recommend handling this at all. It will break properly spelled names.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1366681
ChrisM@home
ChrisM@home
Posted Monday, October 01, 2012 1:01 PM
SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 11:25 PM
Points: 921,
Visits: 3,743
Jeff Moden (10/1/2012)
Actually, I don't recommend handling this at all. It will break properly spelled names.
+1
Low-hanging fruit picker and defender of the moggies
For better assistance in answering your questions, please read
this
.
Understanding and using APPLY, (I)
and
(II)
Paul White
Hidden RBAR: Triangular Joins
/
The "Numbers" or "Tally" Table: What it is and how it replaces a loop
Jeff Moden
Post #1366712
bteraberry
bteraberry
Posted Monday, October 01, 2012 3:31 PM
Ten Centuries
Group: General Forum Members
Last Login: Saturday, February 23, 2013 11:07 AM
Points: 1,103,
Visits: 1,170
Jeff Moden (10/1/2012)
Actually, I don't recommend handling this at all. It will break properly spelled names.
+1
Just because you can do something doesn't mean you should. Doing this sort of data validation within the database will only result in a mess. Even if you have a valid list of names to check against and write the code to "figure out" what they really meant, the chance would remain that two distinct names could be identical after all duplicate letters are removed.
Furthermore, it's not a good architectural philosophy. The application that is the transit between the human who can't spell and the database is the appropriate location for the validation. That is where the 'did you really mean X?' conversation can reliably occur. It is then the database's job to store that data and retrieve it on demand.
└> bt
Forum Etiquette: How to post data/code on a forum to get the best help
Post #1366794
Ray M
Ray M
Posted Monday, October 01, 2012 3:33 PM
UDP Broadcaster
Group: General Forum Members
Last Login: 2 days ago @ 8:25 AM
Points: 1,467,
Visits: 922
+1
GIGO
Post #1366795
Jason-299789
Jason-299789
Posted Tuesday, October 02, 2012 2:02 AM
SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 1:30 AM
Points: 803,
Visits: 2,124
While I agree its not a practical solution, as there are names (Yvonne, Pattinson, Lee, off the top of my head) where consecutive characters can be the same and so you will be destroying potentially valid names
However, I can see its use as a test question in a technical interview for an SQL developer just to see how they think and solve a complex SQL problem.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1366889
Jeff Moden
Jeff Moden
Posted Tuesday, October 02, 2012 6:59 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 32,906,
Visits: 26,793
Jason-299789 (10/2/2012)
While I agree its not a practical solution, as there are names (Yvonne, Pattinson, Lee, off the top of my head) where consecutive characters can be the same and so you will be destroying potentially valid names
However, I can see its use as a test question in a technical interview for an SQL developer just to see how they think and solve a complex SQL problem.
I try not to ask "oolies" during an interview. Asking improbable questions just ticks off the good ones and makes them think the interviewer is just showing off. Ask practical questions. There are plenty of them.
If you want to break the ice on "the next level" of T-SQL programming, explain how important counting is in T-SQL and then ask them to write a script that will count from 1 to 100. You'll be amazed at how many people still resort to a While Loop or a (gasp!) Recursive CTE.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1367005
Jason-299789
Jason-299789
Posted Tuesday, October 02, 2012 7:20 AM
SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 1:30 AM
Points: 803,
Visits: 2,124
As always a Fair point Jeff.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1367022
karthik M
karthik M
Posted Tuesday, October 02, 2012 10:07 PM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 2:56 AM
Points: 2,008,
Visits: 2,469
I do agree all your points. I just thought this one while reading/replying to my another post. As all of you mentioned, it will break the original name into some useless one. I wanted to know the different opinion on this topic from the sql masters. Thats all the intention :)
Jeff,
Coming back to your question, count 1 to 100,
select sum(n) from tally where n < 101
am i right :)
karthik
Post #1367391
dwain.c
dwain.c
Posted Tuesday, October 02, 2012 11:09 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 5:38 AM
Points: 2,345,
Visits: 3,190
karthik M (10/2/2012)
Jeff,
Coming back to your question, count 1 to 100,
select sum(n) from tally where n < 101
am i right :)
How about:
SELECT 50*101
instead?
Seriously, I don't think that was the answer to Jeff's question, but if it was mine's probably faster.
No loops! No CURSORs! No RBAR! Hoo-uh!
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1367405
« Prev Topic
|
Next Topic »
41 posts, Page 1 of 5
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
