topic Re: Converting Numerical value(s) to Character(s) or String(s) - Oracle SQL to Splunk Languages - Part1 in Splunk Search

Converting Numerical value(s) to Character(s) or String(s) - Oracle SQL to Splunk Languages - Part1

syusjk6 — Wed, 12 Dec 2012 07:37:30 GMT

Hi, I am looking for Splunk search languages that might be corresponding to the following SQL: CHAR(13)

Are there any possible ways to get it done?
BTW, CHAR(13) converts 13 to the character corresponding to number 13.
(Oh, correct me if I understood the description of CHAR(13) right.)

Re: Converting Numerical value(s) to Character(s) or String(s) - Oracle SQL to Splunk Languages - Part1

richgalloway — Wed, 12 Dec 2012 13:53:19 GMT

Would you please explain what you want to do with char(13)? Have you tried "\r"?

Re: Converting Numerical value(s) to Character(s) or String(s) - Oracle SQL to Splunk Languages - Part1

yannK — Wed, 12 Dec 2012 14:46:40 GMT

and a conversion can be done with the rex command
example to convert every a to b.
mysearch | rex mode=sed "s/a/b/g"

Re: Converting Numerical value(s) to Character(s) or String(s) - Oracle SQL to Splunk Languages - Part1

Kate_Lawrence-G — Wed, 12 Dec 2012 19:43:44 GMT

I think you want something like this:

sourcetype=mydata | rex "SQL: CHAR(?<char>\d)" | stats values(char)

Re: Converting Numerical value(s) to Character(s) or String(s) - Oracle SQL to Splunk Languages - Part1

syusjk6 — Thu, 13 Dec 2012 01:16:00 GMT

@ richgalloway: I got it wrong. Not char(13) but chr(13)...

Re: Converting Numerical value(s) to Character(s) or String(s) - Oracle SQL to Splunk Languages - Part1

syusjk6 — Thu, 13 Dec 2012 01:17:25 GMT

I put some sample oracle sql on another titled by "Oracle SQL to Splunk Languages - Part 1" with respect to this question. Please refer to it.

Re: Converting Numerical value(s) to Character(s) or String(s) - Oracle SQL to Splunk Languages - Part1

syusjk6 — Thu, 13 Dec 2012 01:18:34 GMT

Thanks. Would you refer to my question put on another title, "Oracle SQL to Splunk Languages - Part1" ?

Re: Converting Numerical value(s) to Character(s) or String(s) - Oracle SQL to Splunk Languages - Part1

yannK — Thu, 13 Dec 2012 16:58:18 GMT

Hi Sysujk6,

Title updated.
If the answer from Ayn solved your problem, do you mind checking the accept button ?

Re: Converting Numerical value(s) to Character(s) or String(s) - Oracle SQL to Splunk Languages - Part1

Kate_Lawrence-G — Thu, 13 Dec 2012 16:58:45 GMT

If I understand your question correctly you are looking for a character value for the number that is inside the CHAR parentheses?

To do that you would need to use the rex I mentioned earlier to get it as a field and then use a lookup table to find the resulting translation. Splunk doesn't have a function for pulling this information directly.

So something like this should work:

lookup.csv 
number, ascii 
13, "carriage return"

sourcetype=mydata | rex "SQL: CHAR(?<char>\d)" | fields char | lookup chart AS number OUTPUT ascii AS "character"

This will take the numeric values you see and translate them to the ascii values you are looking for.

You will have to create the lookup table, but that is just a csv that follows the pattern that laid out.