· 9 years ago · Dec 30, 2016, 06:04 PM
1-- Function that capitalizes strings
2DELIMITER $$
3DROP FUNCTION IF EXISTS CAP_FIRST$$
4CREATE FUNCTION CAP_FIRST (input VARCHAR(255))
5RETURNS VARCHAR(255)
6DETERMINISTIC
7
8BEGIN
9 DECLARE len INT;
10 DECLARE i INT;
11
12 SET len = CHAR_LENGTH(input);
13 SET input = LOWER(input);
14 SET i = 0;
15
16 WHILE (i < len) DO
17 IF (MID(input,i,1) = ' ' OR i = 0) THEN
18 IF (i < len) THEN
19 SET input = CONCAT(
20 LEFT(input,i),
21 UPPER(MID(input,i + 1,1)),
22 RIGHT(input,len - i - 1)
23 );
24 END IF;
25 END IF;
26 SET i = i + 1;
27 END WHILE;
28
29 RETURN input;
30END $$
31DELIMITER ;
32
33MariaDB [brinquedos]> CREATE FUNCTION CAP_FIRST (input VARCHAR(255))
34 -> RETURNS VARCHAR(255)
35 -> DETERMINISTIC
36 ->
37 -> BEGIN
38 -> DECLARE len INT;
39 -> DECLARE i INT;
40 ->
41 -> SET len = CHAR_LENGTH(input);
42 -> SET input = LOWER(input);
43 -> SET i = 0;
44 ->
45 -> WHILE (i < len) DO
46 ->
47Display all 749 possibilities? (y or n)
48? MBREQUAL
49ABS MBRINTERSECTS
50ACOS MBROVERLAPS
51ACTION MBRTOUCHES
52ADD MBRWITHIN
53ADDDATE MD5
54ADDTIME MEDIUM
55AES_DECRYPT MEDIUMBLOB
56AES_ENCRYPT MEDIUMINT
57AFTER MEDIUMTEXT
58AGAINST MERGE
59AGGREGATE MICROSECOND
60ALGORITHM MID
61ALL MIDDLEINT
62ALTER MIGRATE
63ANALYZE MIN
64AND MINUTE
65ANY MINUTE_MICROSECOND
66AREA MINUTE_SECOND
67AS MIN_ROWS
68ASBINARY MLINEFROMTEXT
69ASC MLINEFROMWKB
70ASCII MOD
71ASENSITIVE MODE
72ASIN MODIFIES
73ASTEXT MODIFY
74ASWKB MONTH
75 -> put,i,1) = ' ' OR i = 0) THEN
76 ->
77Display all 749 possibilities? (y or n)
78? MBREQUAL
79ABS MBRINTERSECTS
80ACOS MBROVERLAPS
81ACTION MBRTOUCHES
82ADD MBRWITHIN
83ADDDATE MD5
84ADDTIME MEDIUM
85AES_DECRYPT MEDIUMBLOB
86AES_ENCRYPT MEDIUMINT
87AFTER MEDIUMTEXT
88AGAINST MERGE
89AGGREGATE MICROSECOND
90ALGORITHM MID
91ALL MIDDLEINT
92ALTER MIGRATE
93ANALYZE MIN
94AND MINUTE
95ANY MINUTE_MICROSECOND
96AREA MINUTE_SECOND
97AS MIN_ROWS
98ASBINARY MLINEFROMTEXT
99ASC MLINEFROMWKB
100ASCII MOD
101ASENSITIVE MODE
102ASIN MODIFIES
103ASTEXT MODIFY
104ASWKB MONTH
105ASWKT MONTHNAME
106ATAN MPOINTFROMTEXT
107ATAN2 MPOINTFROMWKB
108AUTO_INCREMENT MPOLYFROMTEXT
109AVG MPOLYFROMWKB
110AVG_ROW_LENGTH MULTILINESTRING
111BACKUP MULTILINESTRINGFROMTEXT
112BDB MULTILINESTRINGFROMWKB
113BEFORE MULTIPOINT
114BEGIN MULTIPOINTFROMTEXT
115BENCHMARK MULTIPOINTFROMWKB
116BERKELEYDB MULTIPOLYGON
117BETWEEN MULTIPOLYGONFROMTEXT
118BIGINT MULTIPOLYGONFROMWKB
119BIN MUTEX
120BINARY NAME
121BINLOG NAMES
122BIT NAME_CONST
123BIT_AND NATIONAL
124BIT_COUNT NATURAL
125BIT_LENGTH NCHAR
126BIT_OR NDB
127BIT_XOR NDBCLUSTER
128BLOB NEW
129BOOL NEXT
130BOOLEAN NO
131BOTH NONE
132BTREE NOT
133BY NOW
134BYTE NO_WRITE_TO_BINLOG
135CACHE NULL
136CALL NULLIF
137CASCADE NUMERIC
138CASCADED NUMGEOMETRIES
139CASE NUMINTERIORRINGS
140CAST NUMPOINTS
141CEIL NVARCHAR
142CEILING OCT
143CENTROID OCTET_LENGTH
144CHAIN OFFSET
145CHANGE OLD_PASSWORD
146CHANGED ON
147CHAR ONE
148CHARACTER OPEN
149CHARACTER_LENGTH OPTIMIZE
150CHARSET OPTION
151CHAR_LENGTH OPTIONALLY
152CHECK OR
153CHECKSUM ORD
154CIPHER ORDER
155CLIENT OUT
156CLOSE OUTER
157COALESCE OUTFILE
158CODE OVERLAPS
159 -> ) THEN
160 ->
161Display all 749 possibilities? (y or n)
162? MBREQUAL
163ABS MBRINTERSECTS
164ACOS MBROVERLAPS
165ACTION MBRTOUCHES
166ADD MBRWITHIN
167ADDDATE MD5
168ADDTIME MEDIUM
169AES_DECRYPT MEDIUMBLOB
170AES_ENCRYPT MEDIUMINT
171AFTER MEDIUMTEXT
172AGAINST MERGE
173AGGREGATE MICROSECOND
174ALGORITHM MID
175ALL MIDDLEINT
176ALTER MIGRATE
177ANALYZE MIN
178AND MINUTE
179ANY MINUTE_MICROSECOND
180AREA MINUTE_SECOND
181AS MIN_ROWS
182ASBINARY MLINEFROMTEXT
183ASC MLINEFROMWKB
184ASCII MOD
185ASENSITIVE MODE
186ASIN MODIFIES
187ASTEXT MODIFY
188ASWKB MONTH
189 -> put = CONCAT(
190 ->
191Display all 749 possibilities? (y or n)
192 -> put,i),
193 ->
194Display all 749 possibilities? (y or n)
195 -> put,i + 1,1)),
196 ->
197Display all 749 possibilities? (y or n)
198 -> put,len - i - 1)
199 ->
200Display all 749 possibilities? (y or n)
201? MBREQUAL
202ABS MBRINTERSECTS
203ACOS MBROVERLAPS
204ACTION MBRTOUCHES
205ADD MBRWITHIN
206ADDDATE MD5
207ADDTIME MEDIUM
208AES_DECRYPT MEDIUMBLOB
209AES_ENCRYPT MEDIUMINT
210AFTER MEDIUMTEXT
211AGAINST MERGE
212AGGREGATE MICROSECOND
213ALGORITHM MID
214ALL MIDDLEINT
215ALTER MIGRATE
216ANALYZE MIN
217AND MINUTE
218ANY MINUTE_MICROSECOND
219AREA MINUTE_SECOND
220AS MIN_ROWS
221ASBINARY MLINEFROMTEXT
222ASC MLINEFROMWKB
223ASCII MOD
224ASENSITIVE MODE
225ASIN MODIFIES
226ASTEXT MODIFY
227ASWKB MONTH
228ASWKT MONTHNAME
229ATAN MPOINTFROMTEXT
230ATAN2 MPOINTFROMWKB
231AUTO_INCREMENT MPOLYFROMTEXT
232AVG MPOLYFROMWKB
233AVG_ROW_LENGTH MULTILINESTRING
234BACKUP MULTILINESTRINGFROMTEXT
235BDB MULTILINESTRINGFROMWKB
236BEFORE MULTIPOINT
237BEGIN MULTIPOINTFROMTEXT
238BENCHMARK MULTIPOINTFROMWKB
239BERKELEYDB MULTIPOLYGON
240BETWEEN MULTIPOLYGONFROMTEXT
241BIGINT MULTIPOLYGONFROMWKB
242BIN MUTEX
243BINARY NAME
244BINLOG NAMES
245BIT NAME_CONST
246BIT_AND NATIONAL
247BIT_COUNT NATURAL
248BIT_LENGTH NCHAR
249BIT_OR NDB
250BIT_XOR NDBCLUSTER
251BLOB NEW
252BOOL NEXT
253BOOLEAN NO
254BOTH NONE
255BTREE NOT
256BY NOW
257BYTE NO_WRITE_TO_BINLOG
258CACHE NULL
259CALL NULLIF
260CASCADE NUMERIC
261CASCADED NUMGEOMETRIES
262CASE NUMINTERIORRINGS
263CAST NUMPOINTS
264CEIL NVARCHAR
265CEILING OCT
266CENTROID OCTET_LENGTH
267CHAIN OFFSET
268CHANGE OLD_PASSWORD
269CHANGED ON
270CHAR ONE
271CHARACTER OPEN
272CHARACTER_LENGTH OPTIMIZE
273CHARSET OPTION
274CHAR_LENGTH OPTIONALLY
275CHECK OR
276CHECKSUM ORD
277CIPHER ORDER
278CLIENT OUT
279CLOSE OUTER
280COALESCE OUTFILE
281CODE OVERLAPS
282COERCIBILITY PACK_KEYS
283COLLATE PARTIAL
284COLLATION PASSWORD
285COLUMN PERIOD_ADD
286COLUMNS PERIOD_DIFF
287COMMENT PHASE
288COMMIT PI
289COMMITTED POINT
290COMPACT POINTFROMTEXT
291COMPRESS POINTFROMWKB
292COMPRESSED POINTN
293CONCAT POLYFROMTEXT
294CONCAT_WS POLYFROMWKB
295CONCURRENT POLYGON
296CONDITION POLYGONFROMTEXT
297CONNECTION POLYGONFROMWKB
298CONNECTION_ID POSITION
299CONSISTENT POW
300CONSTRAINT POWER
301CONTAINS PRECISION
302CONTINUE PREPARE
303CONV PREV
304CONVERT PRIMARY
305CONVERT_TZ PRIVILEGES
306COS PROCEDURE
307COT PROCESS
308COUNT PROCESSLIST
309 -> D IF;
310 ->
311Display all 749 possibilities? (y or n)
312 -> D IF;
313 ->
314Display all 749 possibilities? (y or n)
315? MBREQUAL
316ABS MBRINTERSECTS
317ACOS MBROVERLAPS
318ACTION MBRTOUCHES
319ADD MBRWITHIN
320ADDDATE MD5
321ADDTIME MEDIUM
322AES_DECRYPT MEDIUMBLOB
323AES_ENCRYPT MEDIUMINT
324AFTER MEDIUMTEXT
325AGAINST MERGE
326AGGREGATE MICROSECOND
327ALGORITHM MID
328ALL MIDDLEINT
329ALTER MIGRATE
330ANALYZE MIN
331AND MINUTE
332ANY MINUTE_MICROSECOND
333AREA MINUTE_SECOND
334AS MIN_ROWS
335ASBINARY MLINEFROMTEXT
336ASC MLINEFROMWKB
337ASCII MOD
338ASENSITIVE MODE
339ASIN MODIFIES
340ASTEXT MODIFY
341ASWKB MONTH
342ASWKT MONTHNAME
343ATAN MPOINTFROMTEXT
344ATAN2 MPOINTFROMWKB
345AUTO_INCREMENT MPOLYFROMTEXT
346AVG MPOLYFROMWKB
347AVG_ROW_LENGTH MULTILINESTRING
348BACKUP MULTILINESTRINGFROMTEXT
349BDB MULTILINESTRINGFROMWKB
350BEFORE MULTIPOINT
351BEGIN MULTIPOINTFROMTEXT
352BENCHMARK MULTIPOINTFROMWKB
353BERKELEYDB MULTIPOLYGON
354BETWEEN MULTIPOLYGONFROMTEXT
355BIGINT MULTIPOLYGONFROMWKB
356BIN MUTEX
357BINARY NAME
358BINLOG NAMES
359BIT NAME_CONST
360BIT_AND NATIONAL
361BIT_COUNT NATURAL
362BIT_LENGTH NCHAR
363BIT_OR NDB
364BIT_XOR NDBCLUSTER
365BLOB NEW
366BOOL NEXT
367BOOLEAN NO
368BOTH NONE
369BTREE NOT
370BY NOW
371BYTE NO_WRITE_TO_BINLOG
372CACHE NULL
373CALL NULLIF
374CASCADE NUMERIC
375CASCADED NUMGEOMETRIES
376CASE NUMINTERIORRINGS
377CAST NUMPOINTS
378CEIL NVARCHAR
379CEILING OCT
380CENTROID OCTET_LENGTH
381CHAIN OFFSET
382CHANGE OLD_PASSWORD
383CHANGED ON
384CHAR ONE
385CHARACTER OPEN
386CHARACTER_LENGTH OPTIMIZE
387CHARSET OPTION
388CHAR_LENGTH OPTIONALLY
389CHECK OR
390CHECKSUM ORD
391CIPHER ORDER
392CLIENT OUT
393CLOSE OUTER
394COALESCE OUTFILE
395CODE OVERLAPS
396COERCIBILITY PACK_KEYS
397COLLATE PARTIAL
398COLLATION PASSWORD
399COLUMN PERIOD_ADD
400COLUMNS PERIOD_DIFF
401COMMENT PHASE
402COMMIT PI
403COMMITTED POINT
404COMPACT POINTFROMTEXT
405COMPRESS POINTFROMWKB
406COMPRESSED POINTN
407CONCAT POLYFROMTEXT
408CONCAT_WS POLYFROMWKB
409CONCURRENT POLYGON
410CONDITION POLYGONFROMTEXT
411CONNECTION POLYGONFROMWKB
412CONNECTION_ID POSITION
413CONSISTENT POW
414CONSTRAINT POWER
415CONTAINS PRECISION
416CONTINUE PREPARE
417CONV PREV
418CONVERT PRIMARY
419CONVERT_TZ PRIVILEGES
420COS PROCEDURE
421COT PROCESS
422COUNT PROCESSLIST
423CRC32 PURGE
424CREATE QUARTER
425CROSS QUERY
426CROSSES QUICK
427CUBE QUOTE
428CURDATE RADIANS
429CURRENT_DATE RAND
430CURRENT_TIME READ
431CURRENT_TIMESTAMP READS
432CURRENT_USER REAL
433CURSOR RECOVER
434CURTIME REDUNDANT
435DATA REFERENCES
436DATABASE REGEXP
437DATABASES RELAY_LOG_FILE
438DATE RELAY_LOG_POS
439DATEDIFF RELAY_THREAD
440DATETIME RELEASE
441DATE_ADD RELEASE_LOCK
442DATE_FORMAT RELOAD
443DATE_SUB RENAME
444DAY REPAIR
445DAYNAME REPEAT
446DAYOFMONTH REPEATABLE
447DAYOFWEEK REPLACE
448DAYOFYEAR REPLICATION
449DAY_HOUR REQUIRE
450DAY_MICROSECOND RESET
451DAY_MINUTE RESTORE
452DAY_SECOND RESTRICT
453DEALLOCATE RESUME
454DEC RETURN
455DECIMAL RETURNS
456DECLARE REVERSE
457DECODE REVOKE
458DEFAULT RIGHT
459DEFINER RLIKE
460DEGREES ROLLBACK
461DELAYED ROLLUP
462DELAY_KEY_WRITE ROUND
463DELETE ROUTINE
464DESC ROW
465DESCRIBE ROWS
466DES_DECRYPT ROW_COUNT
467DES_ENCRYPT ROW_FORMAT
468DES_KEY_FILE RPAD
469DETERMINISTIC RTREE
470DIMENSION RTRIM
471DIRECTORY SAVEPOINT
472DISABLE SCHEMA
473DISCARD SCHEMAS
474DISJOINT SECOND
475DISTINCT SECOND_MICROSECOND
476DISTINCTROW SECURITY
477DIV SEC_TO_TIME
478DO SELECT
479DOUBLE SENSITIVE
480DROP SEPARATOR
481DUAL SERIAL
482DUMPFILE SERIALIZABLE
483DUPLICATE SESSION
484DYNAMIC SESSION_USER
485EACH SET
486ELSE SHA
487ELSEIF SHA1
488ELT SHARE
489ENABLE SHOW
490ENCLOSED SHUTDOWN
491ENCODE SIGN
492ENCRYPT SIGNED
493END SIMPLE
494ENDPOINT SIN
495ENGINE SLAVE
496ENGINES SLEEP
497ENUM SMALLINT
498ENVELOPE SNAPSHOT
499EQUALS SOME
500ERRORS SONAME
501ESCAPE SOUNDEX
502ESCAPED SOUNDS
503EVENTS SPACE
504EXECUTE SPATIAL
505EXISTS SPECIFIC
506EXIT SQL
507EXP SQLEXCEPTION
508EXPANSION SQLSTATE
509EXPLAIN SQLWARNING
510EXPORT_SET SQL_BIG_RESULT
511EXTENDED SQL_BUFFER_RESULT
512EXTERIORRING SQL_CACHE
513EXTRACT SQL_CALC_FOUND_ROWS
514FALSE SQL_NO_CACHE
515FAST SQL_SMALL_RESULT
516FETCH SQL_THREAD
517FIELD SQL_TSI_DAY
518FIELDS SQL_TSI_HOUR
519FILE SQL_TSI_MINUTE
520FIND_IN_SET SQL_TSI_MONTH
521FIRST SQL_TSI_QUARTER
522FIXED SQL_TSI_SECOND
523FLOAT SQL_TSI_WEEK
524FLOAT4 SQL_TSI_YEAR
525FLOAT8 SQRT
526FLOOR SRID
527FLUSH SSL
528FOR START
529FORCE STARTING
530FOREIGN STARTPOINT
531FORMAT STATUS
532FOUND STD
533FOUND_ROWS STDDEV
534FROM STDDEV_POP
535FROM_DAYS STDDEV_SAMP
536FROM_UNIXTIME STOP
537FULL STORAGE
538FULLTEXT STRAIGHT_JOIN
539FUNCTION STRCMP
540GEOMCOLLFROMTEXT STRING
541GEOMCOLLFROMWKB STRIPED
542GEOMETRY STR_TO_DATE
543GEOMETRYCOLLECTION SUBDATE
544GEOMETRYCOLLECTIONFROMTEXT SUBJECT
545GEOMETRYCOLLECTIONFROMWKB SUBSTR
546GEOMETRYFROMTEXT SUBSTRING
547GEOMETRYFROMWKB SUBSTRING_INDEX
548GEOMETRYN SUBTIME
549GEOMETRYTYPE SUM
550GEOMFROMTEXT SUPER
551GEOMFROMWKB SUSPEND
552GET_FORMAT SYSDATE
553GET_LOCK SYSTEM_USER
554GLENGTH TABLE
555GLOBAL TABLES
556GRANT TABLESPACE
557GRANTS TAN
558GREATEST TEMPORARY
559 -> D WHILE;
560 ->
561 -> RETURN input;
562 -> END $$
563ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'i,1) = ' ' OR i = 0) THEN
564 ) THEN
565 put = CONCAT(
566 put,i),
567 put,i + 1' at line 14
568MariaDB [brinquedos]> DELIMITER ;