How to concatenate strings with padding in sqlite

by

The || operator is “concatenate” – it joins together the two strings of
its operands.

From http://www.sqlite.org/lang_expr.html

For padding, the seemingly-cheater way I’ve used is to start with your target string, say ‘0000’, concatenate ‘0000423’, then substr(result, -4, 4) for ‘0423’.

Update: Looks like there is no native implementation of “lpad” or “rpad” in SQLite, but you can follow along (basically what I proposed) here: http://verysimple.com/2010/01/12/sqlite-lpad-rpad-function/

-- the statement below is almost the same as
-- select lpad(mycolumn,'0',10) from mytable

select substr('0000000000' || mycolumn, -10, 10) from mytable

-- the statement below is almost the same as
-- select rpad(mycolumn,'0',10) from mytable

select substr(mycolumn || '0000000000', 1, 10) from mytable

Here’s how it looks:

SELECT col1 || '-' || substr('00'||col2, -2, 2) || '-' || substr('0000'||col3, -4, 4)

it yields

"A-01-0001"
"A-01-0002"
"A-12-0002"
"C-13-0002"
"B-11-0002"

More Related Contents:

  1. Sqlite convert string to date
  2. Making a string concatenation operator in R
  3. How do I concatenate strings?
  4. How to efficiently concatenate strings in go
  5. How do I concatenate strings in Swift?
  6. How to collapse a list of characters into a single string in R
  7. Python Pandas concatenate a Series of strings into one string
  8. Efficiently repeat a character/string n times in Scala
  9. What is the difference between VBScript’s + and & operator?
  10. Why does my string not match when reading user input from stdin?
  11. Concatenating null strings in Java [duplicate]
  12. What’s the difference of strings within single or double quotes in groovy?
  13. Difference between text and varchar (character varying)
  14. Find string between two substrings [duplicate]
  15. How can I split multiple joined words?
  16. Multiline strings in VB.NET
  17. Case insensitive comparison of strings in shell script
  18. Why is capitalizing the first letter of a string so convoluted in Rust?
  19. How to retrieve two columns data in A,B format in Oracle
  20. How do you write multiline strings in Go?
  21. Algorithm to find articles with similar text
  22. How to remove leading and trailing zeros in a string? Python
  23. Is there a function to split a string in Oracle PL/SQL?
  24. Line continuation of strings in Fortran
  25. How to parse string to date in xslt 2.0
  26. Convert slice of string to slice of pointer to string
  27. The History Behind the Definition of a ‘String’ [closed]
  28. How can I replace newline characters using JSP and JSTL?
  29. Remove all line breaks at the beginning of a string in Swift
  30. Can I convert a string to enum without macros in Rust?

Leave a Comment