0

In SQL Server, I am trying to extract some text in a column that contains comments. I wrote the code below which essentially looks for everything after the substring "ABILITIES:" and everything before a "."

The code works, but because of the contents of that column I had to convert every reference to it in varchar format, i.e. CONVERT(VARCHAR(MAX),comment)

And as you can see, the code becomes very long and hard to read. My question is, is there a way to create a temporary column that basically just converts the comment column to varchar. And then I can just use that temp column as a variable in my code? Sort of like how you would with Python?

Or will you have to do that through a CTE?

SELECT *,
REPLACE(
    LEFT(
        SUBSTRING(CONVERT(VARCHAR(MAX),comment), CHARINDEX('ABILITIES:', CONVERT(VARCHAR(MAX),comment))+12, LEN(CONVERT(VARCHAR(MAX),comment)) - CHARINDEX('ABILITIES:', CONVERT(VARCHAR(MAX),comment))+12),
        CASE 
            WHEN CHARINDEX('.', SUBSTRING(CONVERT(VARCHAR(MAX),comment), CHARINDEX('ABILITIES:', CONVERT(VARCHAR(MAX),comment))+12, LEN(CONVERT(VARCHAR(MAX),comment)) - CHARINDEX('ABILITIES:', CONVERT(VARCHAR(MAX),comment))+12)) = 0
                THEN 0
            ELSE CHARINDEX('.', SUBSTRING(CONVERT(VARCHAR(MAX),comment), CHARINDEX('ABILITIES:', CONVERT(VARCHAR(MAX),comment))+12, LEN(CONVERT(VARCHAR(MAX),comment)) - CHARINDEX('ABILITIES:', CONVERT(VARCHAR(MAX),comment))+12)) - 1 
            END
    ), ',','') extracted_text
    
FROM table
sudden_clarity_clarence
  • 303
  • 4
  • 10
  • You can put it in a TEMP table with some ID that corresponds to the main table, then anytime you need to reference the main table again you can join your temp table to it. – Brad May 19 '23 at 13:56
  • You can use a [WITH common_table_expression (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16). But why do you have to convert the comment to VARCHAR(MAX)? – Olivier Jacot-Descombes May 19 '23 at 13:57
  • While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky May 19 '23 at 13:57

1 Answers1

1

You could use apply() and a values constructor.

select <use column Mycol as much as you like>
from table t
cross apply(values(convert(varchar(max), comment)))x(Mycol);
Stu
  • 30,392
  • 6
  • 14
  • 33
  • I would suggest OUTER APPLY instead (and also I didn't understand how that is working for the OP. Probably she\he has very specific comments to make it possible). – Cetin Basoz May 19 '23 at 14:10
  • How would outer apply make a difference the values table constructor will always return a row – Stu May 19 '23 at 14:13
  • You are right, my bad. – Cetin Basoz May 19 '23 at 14:20
  • @CetinBasoz There is a difference between them in this case: `CROSS APPLY(VALUES` tends to get inlined into the outer query completely, whereas I find `OUTER APPLY(VALUES` tends to get turned into a `Constant Scan -> Left Join`. Each has pros and cons: very complex, slow expressions are better not inlined, but most are better inlined – Charlieface May 19 '23 at 14:31