llmflow (version 3.0.2)

extract_sql_code: Extract SQL code from a string

Description

This function extracts SQL code from a string by matching all content between '```sql' and '```' (case-insensitive).

Usage

extract_sql_code(input_string)

Value

A character vector containing the extracted SQL code

Arguments

input_string

A string containing SQL code blocks, typically a response from an LLM

Examples

# Simple SQL query
text <- "Query:\n```sql\nSELECT * FROM users;\n```"
extract_sql_code(text)

# Case-insensitive matching
text <- "```SQL\nSELECT COUNT(*) FROM orders;\n```"
extract_sql_code(text)

# Multiple SQL blocks
response <- "
Create table:
```sql
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);
```

Insert data:
```sql
INSERT INTO employees (id, name, department, salary)
VALUES
    (1, 'John Doe', 'IT', 75000),
    (2, 'Jane Smith', 'HR', 65000);
```

Query data:
```sql
SELECT name, salary
FROM employees
WHERE department = 'IT'
ORDER BY salary DESC;
```
"
codes <- extract_sql_code(response)
length(codes) # Returns 3

# Complex query with joins
complex_query <- "
Here's the analysis query:
```sql
WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) as month,
        SUM(total_amount) as total_sales,
        COUNT(DISTINCT customer_id) as unique_customers
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    month,
    total_sales,
    unique_customers,
    total_sales / unique_customers as avg_per_customer
FROM monthly_sales
ORDER BY month;
```
"
extract_sql_code(complex_query)