CODEWITHSUNDEEP
sqlpostgresql
bigbiff
bigbiff

Reputation: 75

Extract first word from a varchar column and reverse it

I have following data in my table 

id nml               
-- ----------------- 
1  Temora sepanil    
2  Human Mixtard     
3  stlliot vergratob

I need to get the result by extracting first word in column nml and get its last 3 characters with reverse order

That means output should be like

nml               reverse 
----------------- ------- 
Temora sepanil    aro     
Human Mixtard     nam     
stlliot vergratob toi

Upvotes: 0

Views: 1924

Answers (3)

japzdivino
japzdivino

Reputation: 1746

You can use the SUBSTRING, CHARINDEX, RIGHT and REVERSE function

here's the syntax

  REVERSE(RIGHT(SUBSTRING(nml , 1, CHARINDEX(' ', nml) - 1),3))

sample:

  SELECT REVERSE(RIGHT(SUBSTRING(nml , 1, CHARINDEX(' ', nml) - 1),3)) AS 'Reverse' 
  FROM TableNameHere

Upvotes: 0

hkbharath
hkbharath

Reputation: 327

  1. Split nml using regexp_split_to_array(string text, pattern text [, flags text ]) refer Postgres Doc for more info.
  2. Use reverse(str) (refer Postgres Doc) to reverse the first word form previous split.
  3. Use substr(string, from [, count]) (refer Postgres Doc) to select first three letters of the reversed test

Query

SELECT 
    nml,
    substr(reverse(regexp_split_to_array(nml, E'\\s+')[0]),3) as reverse
FROM
    MyTable

Upvotes: 0

Vivek S.
Vivek S.

Reputation: 21885

You use PostgreSQL's string functions to achieve desired output

in this case am using split_part,right,reverse function

select reverse(right(split_part('Temora sepanil',' ',1),3))

output:

aro

so you can write your query in following format

select nml
      ,reverse(right(split_part(nml,' ',1),3)) "Reverse" 
from tbl

Upvotes: 1

Related Questions