Less puzzling puzzles please 2

[adamroof]

Do the powers that be have a tad easier puzzle for us who are mathematically deficient and tsql inept, but want to learn more?

Im liking them so far, but the only one i could come up with was the hours and day thing.

 

[SQLBill]

The easier puzzles are the daily questions that members ask. Some of us were looking for the really unique or tricky SQL Server questions/solutions.

-SQLBill

Posting advice: FAQ481-4875

 

[adamroof]

im not knocking the puzzles, awesome you have a place to fine tune your skills.

I dont want to mess with members production work related problems, and post something that wont help them get to their point quickly, ive done that, and most of my replies here make me look like a foo!

An "unreal" world example i guess its called.

I'll check the forums listings for what im looking for, if not, maybe we could make one.

 

[SQLDenis]

Ok here we go

CREATE TABLE #Names (ID INT identity not null,NameField VARCHAR(50), ProperNameField VARCHAR(50))

INSERT INTO #Names
SELECT 'Klein, Barbara',NULL
UNION ALL
SELECT 'Smith, John',NULL
UNION ALL
SELECT 'Jackson, Michael',NULL
UNION ALL
SELECT 'Gates, Bill',NULL

expected output

Barbara Klein
John Smith
Michael Jackson
Bill Gates

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[tanman1975]

HEY! That table breaks all sorts of normalization rules

 

[adamroof]

Ive done something like that before! Yeah!

UPDATE #Names SET ProperNameField = 
(LTRIM(SUBSTRING(NameField,CHARINDEX(',',NameField,1)+ 1,20))) + ' ' +
(RTRIM(SUBSTRING(NameField,0,CHARINDEX(',',NameField,1))))

SELECT * FROM #Names

 

[earthandfire]

I find myself somewhere in the middle. I agree with adamroof, that some of the puzzles are rather more challenging for beginners (of I which I am definitely one) than I would like. In fact with one of puzzles I scarcely understood the question, but I would be loathe to see the level dropped. Where I can I like to attempt it, even if I don't post a solution.

I think however that posting a puzzle on Monday for results on Friday as was originally suggested gives those of us who are less skilled in TSQL a fighting chance.

Additionally, as Denis has just done, a gentle puzzle (with some degree of challenge) would be great. Perhaps the TSQL maestros here could set two puzzles each week. One for mere mortals and one for the masochists .

 

[DotNetGnat]

Select Substring(NameField,Charindex(' ',Namefield)+1,50)+' '+Substring(NameField,1,Charindex(',',Namefield)-1)
from #Names

-DNG

 

[vongrunt]

Kewl.

Puzzle Lite #2: swap values in NameField and ProperNameField columns - with a single statement

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[vongrunt]

For DNG: insert this record:

insert into #names values ('Godzilla', null)

What happens?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[adamroof]

#2

UPDATE #Names SET ProperNameField = NameField, NameField = 
(LTRIM(SUBSTRING(NameField,CHARINDEX(',',NameField,1)+ 1,20))) + ' ' +
(RTRIM(SUBSTRING(NameField,0,CHARINDEX(',',NameField,1))))

or do you mean the whole thing in one statement?

 

[vongrunt]

This is one statement... but isn't second part (NameField=...) a bit complex?

(hint: [!]swap[/!])

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[adamroof]

ahh...swap away
can be run over and over and over...

UPDATE #Names SET ProperNameField = NameField, NameField = ProperNameField

 

[DotNetGnat]

i got this error von

Invalid length parameter passed to the substring function.
The statement has been terminated.

-DNG

 

[adamroof]

Sorry i missed out on the inception of the puzzles. It would be awesome if you could do a SQL Puzzles(lite). And as earthandfire stated, please, whatever way you go, do not stop your puzzles pro!

 

[DotNetGnat]

Von,

thanks for correcting me...got it right with this:

Update #names 
   SET ProperNameField= Substring(NameField,Charindex(' ',Namefield)+1,20)
      +' '+
      Substring(NameField,0,Charindex(',',Namefield))

-DNG

 

[vongrunt]

Maybe we should approach that another way...

Most of posts here follow this pattern:

1) I have a problem
2) Here is the answer
3) Thanks. Bye!

Result: people learn how to do things, not why to do things this-or-that way. So... how about some ol'-fashioned discussions? Someone ask question that makes him/her , we all together find "why" part and then proceed with "how" - with possible Puzzles Lite (tm) somewhere between these two steps.

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[adamroof]

ok, i have a why and how...

i ran Charindex(' ',Namefield)+1,20) with same results as my
(LTRIM(SUBSTRING(NameField,CHARINDEX(',',NameField,1)+ 1,20)))

what if a value of 'smith,bob' was entered?

would DNG be incorrect, or myself, or neither?
why?
would it just depend on data integrity or better to not worry about it.
how?
whats the best way to check a table full of values for something that can be depended on?