API tools faq
paste
Guest User

Untitled

a guest
Aug 18th, 2018
112
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.96 KB | None | 0 0
raw download clone embed print report
  1. ------------------------SQL Server-------------------------------------------------------------------------------------
  2.  
  3. ----1) Get the first letter of the string & capitalize it
  4. upper(left(ltrim(title),1)) as first_letter_first_word,
  5.  
  6. ----2) Get the remaining string
  7. right(lower(ltrim(title)), len(lower(ltrim(title)))-1) as remaining_string,
  8.  
  9. ----3) Find the position of the first blank space-----------------
  10. charindex(' ', right(lower(ltrim(title)), len(lower(ltrim(title)))-1)) as blank_position,
  11.  
  12. ----4) Get the remainig firs word /without its first letter/
  13. left(right(lower(ltrim(title)), len(lower(ltrim(title)))-1),
  14. charindex(' ', right(lower(ltrim(title)), len(lower(ltrim(title)))-1)) ) as remaing_first_word,
  15.  
  16. ----5) Separate the second word from the rest of the strin--------------
  17. right(right(lower(ltrim(title)), len(lower(ltrim(title)))-1),
  18. len(right(lower(ltrim(title)), len(lower(ltrim(title)))-1)) -
  19. charindex(' ', right(lower(ltrim(title)), len(lower(ltrim(title)))-1))) as second_word,
  20.  
  21. ----6) Get the first letter of the second word and capitalize it--------------
  22. upper(left(right(right(lower(ltrim(title)), len(lower(ltrim(title)))-1),
  23. len(right(lower(ltrim(title)), len(lower(ltrim(title)))-1)) -
  24. charindex(' ', right(lower(ltrim(title)), len(lower(ltrim(title)))-1))), 1)) as first_letter_second_word,
  25.  
  26. -----7) get the remaining second word----------------------------------------
  27. right(right(right(lower(ltrim(title)), len(lower(ltrim(title)))-1),
  28. len(right(lower(ltrim(title)), len(lower(ltrim(title)))-1)) -
  29. charindex(' ', right(lower(ltrim(title)), len(lower(ltrim(title)))-1))),
  30.  
  31. len(right(right(lower(ltrim(title)), len(lower(ltrim(title)))-1),
  32. len(right(lower(ltrim(title)), len(lower(ltrim(title)))-1)) -
  33. charindex(' ', right(lower(ltrim(title)), len(lower(ltrim(title)))-1)))) - 1) as remining_second_word,
  34.  
  35.  
  36. -----------------------------------------------------------------------------------------------------------------------------
  37. ---first letter first word------
  38. upper(left(ltrim(title),1)) +
  39.  
  40. ---remaining first word--------------
  41. left(right(lower(ltrim(title)), len(lower(ltrim(title)))-1),
  42. charindex(' ', right(lower(ltrim(title)), len(lower(ltrim(title)))-1)) ) +
  43.  
  44. ---second word (with capital letter----------
  45. concat(
  46. ---first letter to be replaces
  47. upper(left(right(right(lower(ltrim(title)), len(lower(ltrim(title)))-1),
  48. len(right(lower(ltrim(title)), len(lower(ltrim(title)))-1)) -
  49. charindex(' ', right(lower(ltrim(title)), len(lower(ltrim(title)))-1))), 1) ),
  50.  
  51.  
  52. right(right(right(lower(ltrim(title)), len(lower(ltrim(title)))-1),
  53. len(right(lower(ltrim(title)), len(lower(ltrim(title)))-1)) -
  54. charindex(' ', right(lower(ltrim(title)), len(lower(ltrim(title)))-1))),
  55.  
  56. len(right(right(lower(ltrim(title)), len(lower(ltrim(title)))-1),
  57. len(right(lower(ltrim(title)), len(lower(ltrim(title)))-1)) -
  58. charindex(' ', right(lower(ltrim(title)), len(lower(ltrim(title)))-1)))) - 1)
  59. ) as final_proper
  60.  
  61. from datageeking.dbo.films
  62.  
  63.  
  64. -------------------------MySQL--------------------------------------------------------------------------------------------------
  65. #----1) Get the first letter of the string & capitalize it
  66. upper(left(ltrim(title),1)) as first_letter_first_word,
  67.  
  68.  
  69. #----2) Get the remaining string
  70. right(lower(ltrim(title)), length(lower(ltrim(title)))-1) as remaining_string,
  71.  
  72.  
  73. #----3) Find the position of the first blank space-----------------
  74. instr(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), ' ') as blank_position,
  75.  
  76.  
  77. #----4) Get the remainig firs word /without its first letter/
  78. left(right(lower(ltrim(title)), length(lower(ltrim(title)))-1),
  79. instr(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), ' ') ) as remaing_first_word,
  80.  
  81.  
  82. #----5) Separate the second word from the rest of the strin--------------
  83. right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1),
  84. length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) -
  85. instr(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), ' ') ) as second_word,
  86.  
  87.  
  88. #----6) Get the first letter of the second word and capitalize it--------------
  89. upper(left(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1),
  90. length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) -
  91. instr(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), ' ')), 1)) as first_letter_second_word,
  92.  
  93. #-----7) Get the remaining second word-------------------------------------------
  94. right(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1),
  95. length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) -
  96. instr(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), ' ')),
  97.  
  98. length(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1),
  99. length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) -
  100. instr(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), ' '))) - 1) as remining_second_word,
  101.  
  102. #---------------------------------------------------------------------------------------------------------
  103. #---first letter first word------
  104. Concat (upper(left(ltrim(title),1)),
  105.  
  106. #---remaining first word--------------
  107. left(right(lower(ltrim(title)), length(lower(ltrim(title)))-1),
  108. instr(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), ' ')),
  109.  
  110. #---first letter second word---------------------------------------------------------
  111. upper(left(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1),
  112. length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) -
  113. instr(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), ' ')), 1)),
  114.  
  115. #---remaining second word----------------------------------------------------------------
  116. right(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1),
  117. length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) -
  118. instr(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), ' ')),
  119.  
  120. length(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1),
  121. length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) -
  122. instr(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), ' '))) - 1)
  123. ) as final_proper
  124.  
  125. from sakila.film
  126.  
  127.  
  128. ------------------------------PostgreSQL-----------------------------------------------------------------------------------------------
  129. ----1) Get the first letter of the string & capitalize it
  130. upper(left(ltrim(title),1)) as first_letter_first_word,
  131.  
  132.  
  133. ----2) Get the remaining string
  134. right(lower(ltrim(title)), length(lower(ltrim(title)))-1) as remaining_string,
  135.  
  136.  
  137. ----3) Find the position of the first blank space-----------------
  138. position(' ' in right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) as blank_position,
  139.  
  140.  
  141. ----4) Get the remainig firs word /without its first letter/
  142. left(right(lower(ltrim(title)), length(lower(ltrim(title)))-1),
  143. position(' ' in right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) ) as remaing_first_word,
  144.  
  145.  
  146. ----5) Separate the second word from the rest of the strin--------------
  147. right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1),
  148. length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) -
  149. position(' ' in right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) ) as second_word,
  150.  
  151.  
  152. ----6) Get the first letter of the second word and capitalize it--------------
  153. upper(left(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1),
  154. length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) -
  155. position(' ' in right(lower(ltrim(title)), length(lower(ltrim(title)))-1))), 1)) as first_letter_second_word,
  156.  
  157.  
  158. -----7) Get the remaining second word-------------------------------------------
  159. right(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1),
  160. length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) -
  161. position(' ' in right(lower(ltrim(title)), length(lower(ltrim(title)))-1))),
  162.  
  163. length(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1),
  164. length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) -
  165. position(' ' in right(lower(ltrim(title)), length(lower(ltrim(title)))-1)))) - 1) as remining_second_word,
  166.  
  167. ---------------------------------------------------------------------------------------------------------
  168. ---first letter first word------
  169. Concat (upper(left(ltrim(title),1)),
  170.  
  171. ---remaining first word--------------
  172. left(right(lower(ltrim(title)), length(lower(ltrim(title)))-1),
  173. position(' ' in right(lower(ltrim(title)), length(lower(ltrim(title)))-1))),
  174.  
  175. -----first letter second word---------------------------------------------------------
  176. upper(left(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1),
  177. length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) -
  178. position(' ' in right(lower(ltrim(title)), length(lower(ltrim(title)))-1))), 1)),
  179.  
  180. ------remaining second word----------------------------------------------------------------
  181. right(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1),
  182. length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) -
  183. position(' ' in right(lower(ltrim(title)), length(lower(ltrim(title)))-1))),
  184.  
  185. length(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1),
  186. length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) -
  187. position(' ' in right(lower(ltrim(title)), length(lower(ltrim(title)))-1)))) - 1)
  188. ) as final_proper
  189.  
  190. from public.film
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!