CODEWITHSUNDEEP
stringsnowflake-cloud-data-platform
Maran
Maran

Reputation: 171

Snowflake appending string at the beginning

I have a column which holds values as below from which I wanted to check if the first character is R after removing the leading zeros, If the first character is not equal to R then add at the beginning as shown in the expected values.

Column value
------------
00002349954
123456
R34854-1
R1234-3
01278993
345677

I want the above values to be formed as below. Appreciate any help on this. Thanks

Expected Values
---------------
R2349954
R123456
R34854-1
R1234-3
R1278993
R345677

Upvotes: 0

Views: 748

Answers (2)

Marcel
Marcel

Reputation: 2612

Assuming there are no whitespaces in your values you may use this:

  1. Remove leading zeros: REPLACE(LTRIM(REPLACE(colname, '0', ' ')), ' ', '0')
  2. Add a leading "R": CONCAT('R', colname)

In your case to meet your requirements something like the following should work:

SELECT 
 CASE 
 WHEN REPLACE(LTRIM(REPLACE(colname, '0', ' ')), ' ', '0') like 'R%' THEN REPLACE(LTRIM(REPLACE(colname, '0', ' ')), ' ', '0')
 ELSE CONCAT('R',REPLACE(LTRIM(REPLACE(colname, '0', ' ')), ' ', '0'))

Upvotes: 0

Gokhan Atil
Gokhan Atil

Reputation: 10039

Try this:

select 'R' || ltrim(v,'0R' ) 
from values ('00002349954'),('R1234-3'),('123456') tmp(v);

+-----------------------+
| 'R' || LTRIM(V,'0R' ) |
+-----------------------+
| R2349954              |
| R1234-3               |
| R123456               |
+-----------------------+

LTRIM https://docs.snowflake.com/en/sql-reference/functions/ltrim.html

Upvotes: 2

Related Questions