Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 671
  • Last Modified:

SQL SELECT CASE & REPLACE() for text change / sorting

I've assigned a coloum called ptmorphraw to rework the post_title value for sorting. However, when I apply the query the replace values come up as null.

Essentially I need to replace the value so the text...Single Stream, Double Stream etc, become
1 Stream, 2 Stream

SELECT CASE WHEN a.post_title LIKE  '%Single Stream%'
THEN REPLACE( a.post_title,  'Single Stream',  '1 Stream' ) 
WHEN a.post_title LIKE  '%Double Stream%'
THEN REPLACE( a.post_title,  'Double Stream',  '2 Stream' ) 
END AS ptmorphraw, a.post_title AS ptmorph, a . * , d . * 
FROM wp_prefix_posts a
INNER JOIN wp_prefix_term_relationships e ON a.ID = e.object_id
INNER JOIN wp_prefix_term_taxonomy c ON c.term_taxonomy_id = e.term_taxonomy_id
INNER JOIN wp_prefix_terms d ON c.term_id = d.term_id
WHERE e.object_id = a.ID
AND a.post_type =  'recyclingbins'
AND a.post_status =  'publish'
AND d.slug =  'steel'
GROUP BY a.id
ORDER BY ptmorphraw ASC 

Open in new window

0
m2ew
Asked:
m2ew
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
You missed to put an else condition on your case statement

CASE WHEN a.post_title LIKE  '%Single Stream%'
THEN REPLACE( a.post_title,  'Single Stream',  '1 Stream' )
WHEN a.post_title LIKE  '%Double Stream%'
THEN REPLACE( a.post_title,  'Double Stream',  '2 Stream' )
ELSE a.post_title
END AS ptmorphraw,
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
and your order by to work , should contain the same case statement instead of the column name
0
 
m2ewAuthor Commented:
@aneeshattingal

Brilliant! Knew it had to be something silly. Been staring at the query too long.
Thanks.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now