Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

palindrome Expand / Collapse
Author
Message
Posted Sunday, October 20, 2013 1:36 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:06 AM
Points: 20,858, Visits: 32,878
Jeff Moden (10/20/2013)
sqlserver12345 (10/20/2013)
I do not want to use reverse function.please check the code I posted and correct what wrong I did.thanks


I'm curious... please explain why you don't want to use "REVERSE".

I also have to ask what your intended definition of a "palindrome" actually is. For example, the following is a palindrome.

Madam I'm Adam

None of the solutions so far take into account any punctuation or spaces.

Also, do you consider a single letter to be a palindrome?


I never thought about taking out punctuation or spaces. Shouldn't be that hard to implement, just not going to do the additional work. I am curious why he doesn't want to use REVERSE either. I haven't seen a response to my question on that one either.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1506509
Posted Sunday, October 20, 2013 2:11 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:17 PM
Points: 1,095, Visits: 3,182
The 'no reverse' is an interesting requirement. Here's my attempt at it
with stringList as (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) ID, String,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(String,' ',''),'.',''),',',''),'''',''),'-','') Compact --More replaces are likely
FROM (VALUES ('bob'),('Madam, I''m Adam'),('noon'),('fail'),('a')) AS I(String)
),
cteTally as (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t1(N),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t2(N)
)
SELECT ID, String, CASE WHEN SUM(c) = LEN(Compact)/2 THEN 'Palidrome' ELSE 'Not Palidrome' END C
FROM (
SELECT ID, String, Compact, CASE WHEN SUBSTRING(Compact,N,1) = SUBSTRING(Compact,LEN(Compact) - N + 1, 1) THEN 1 ELSE 0 END c
FROM stringList s
OUTER APPLY (SELECT TOP(LEN(Compact)/2) N FROM cteTally) t
) a
GROUP BY ID, String, Compact;

Post #1506513
Posted Sunday, October 20, 2013 5:01 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,770, Visits: 32,437
mickyT (10/20/2013)
The 'no reverse' is an interesting requirement.


I believe what we have here is homework about how to use WHILE loops and the instructor probably never heard of a Tally or Numbers table. Although it's good for people in the tuning business, I wish instructors would get a clue about such "pseudo cursors".


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1506519
Posted Sunday, October 20, 2013 5:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,770, Visits: 32,437
sqlserver12345 (10/18/2013)
declare @count int
declare @charvaluefront varchar
declare @charvalueback varchar
declare @frontpos int
declare @backpos int
select @count=len('ABC')
set @frontpos=1
set @backpos=@count
while(@frontpos<=@count)
begin
select @charvaluefront=substring('ABC',@frontpos,1)
select @charvalueback=substring('ABC',@backpos,1)
set @frontpos=@frontpos+1
set @backpos=@backpos-1
print @charvaluefront
print @charvalueback
end

How do I compare @charvaluefront and @charvalueback values to test if the values are equal and print that its a palindrome.
I tried the if clause but it does not work.Any suggestions please


Ok, the more I think about this and the "No REVERSE" requirement, the more I think this is homework and the instructor wants you to use WHILE loops. Are you being required to use a WHILE loop or not because, in real life, I wouldn't go anywhere near a WHILE loop for this. Of course, in real life, I'd be using a REVERSE after I cleaned the string.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1506521
Posted Sunday, October 20, 2013 5:55 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:17 PM
Points: 1,095, Visits: 3,182
Jeff Moden (10/20/2013)
mickyT (10/20/2013)
The 'no reverse' is an interesting requirement.


I believe what we have here is homework about how to use WHILE loops and the instructor probably never heard of a Tally or Numbers table. Although it's good for people in the tuning business, I wish instructors would get a clue about such "pseudo cursors".
I suspected the same, you don't usually strike such restrictions in real life It does however provide some good little brain stretchers occasionally.
Post #1506522
Posted Sunday, October 20, 2013 6:34 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 3,438, Visits: 5,390
sqlserver12345 (10/20/2013)
I do not want to use reverse function.please check the code I posted and correct what wrong I did.thanks


That's exactly what we're doing by showing you the right way! Assuming this is homework, you should be challenging your instructor by delivering outstanding results.

Easy if you happen to have a pattern-based string splitter in your toolbox.

WITH SampleData (palindrome) AS
(
SELECT 'Madam I''m Adam'
UNION ALL SELECT 'ABC'
UNION ALL SELECT 'ABBA'
UNION ALL SELECT 'A man, a plan, a canal - Panama!'
UNION ALL SELECT 'Madam in Eden, I''m Adam'
UNION ALL SELECT 'Doc, note: I dissent. A fast never prevents a fatness. I diet on cod'
UNION ALL SELECT 'Never odd or even'
),
SplitStrings AS
(
SELECT palindrome
,RemovePuncuation=
(
SELECT Item + ''
FROM SampleData b
CROSS APPLY dbo.PatternSplitCM(palindrome, '[A-Za-z]')
WHERE a.palindrome = b.palindrome AND [Matched] = 1
ORDER BY ItemNumber
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'
)
FROM SampleData a
)
SELECT palindrome
,IsPalindrome=CASE WHEN REVERSE(RemovePuncuation) = RemovePuncuation THEN 1 ELSE 0 END
FROM SplitStrings;


The PatternSplitCM FUNCTION can be found in the fourth link in my signature.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1506524
Posted Sunday, October 20, 2013 8:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,770, Visits: 32,437
dwain.c (10/20/2013)
sqlserver12345 (10/20/2013)
I do not want to use reverse function.please check the code I posted and correct what wrong I did.thanks


That's exactly what we're doing by showing you the right way! Assuming this is homework, you should be challenging your instructor by delivering outstanding results.

Easy if you happen to have a pattern-based string splitter in your toolbox.

WITH SampleData (palindrome) AS
(
SELECT 'Madam I''m Adam'
UNION ALL SELECT 'ABC'
UNION ALL SELECT 'ABBA'
UNION ALL SELECT 'A man, a plan, a canal - Panama!'
UNION ALL SELECT 'Madam in Eden, I''m Adam'
UNION ALL SELECT 'Doc, note: I dissent. A fast never prevents a fatness. I diet on cod'
UNION ALL SELECT 'Never odd or even'
),
SplitStrings AS
(
SELECT palindrome
,RemovePuncuation=
(
SELECT Item + ''
FROM SampleData b
CROSS APPLY dbo.PatternSplitCM(palindrome, '[A-Za-z]')
WHERE a.palindrome = b.palindrome AND [Matched] = 1
ORDER BY ItemNumber
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'
)
FROM SampleData a
)
SELECT palindrome
,IsPalindrome=CASE WHEN REVERSE(RemovePuncuation) = RemovePuncuation THEN 1 ELSE 0 END
FROM SplitStrings;


The PatternSplitCM FUNCTION can be found in the fourth link in my signature.


The problem is, you used REVERSE which the OP said he couldn't do. Heh... you also forget that a Palindrome can contain numeric digits.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1506528
Posted Sunday, October 20, 2013 8:50 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 3,438, Visits: 5,390
Jeff Moden (10/20/2013)

The problem is, you used REVERSE which the OP said he couldn't do. Heh... you also forget that a Palindrome can contain numeric digits.


Nah... didn't forget. Ignored is more like it. We still haven't heard a good reason why REVERSE is not allowed.

Change the pattern matching string to [a-zA-Z0-9] to include digits.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1506530
Posted Monday, October 21, 2013 11:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
nothing just ...just learning some coding
Post #1506765
Posted Monday, October 21, 2013 1:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,770, Visits: 32,437
sqlserver12345 (10/21/2013)
nothing just ...just learning some coding


In that case, I strongly recommend that you drop the whole idea of using a WHILE loop and not using REVERSE. Might as well learn the right way to do something like this.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1506834
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse