Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


palindrome


palindrome

Author
Message
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24245 Visits: 37978
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.

Cool
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)
mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 3309
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;


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45215 Visits: 39926
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45215 Visits: 39926
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 3309
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.
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4275 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45215 Visits: 39926
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. :-D

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4275 Visits: 6431
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. :-D


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. :-D


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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
sqlnewbie17
sqlnewbie17
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 1199
nothing just ...just learning some coding
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45215 Visits: 39926
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search