Search code examples
sqlsql-server-2012substrcharindex

SQL does not take in positive strings

I am using SQL Server 2012. I have a trap where I get the minus and put it at the front of my values in a set string, however there are also values that don't have a minus, and they come up as nulls in my column. I need a way to not take in these positive values as it is a standard procedure and I only need the minus values in my query.

My code is

CASE 
   WHEN CHARINDEX('-', SUBSTRING([Txt], 147, 10)) > 0   
      THEN '-' + REPLACE(LTRIM(RTRIM(SUBSTRING([TXT], 147, 9))), ',','') 
END AS [Open_Amount]

In my query the positive values come up as "nulls" which I don't want, I want the entire row ignored if open_amount is a positive value. Is there a specific else statement I can put in my case or is there another way? If it helps using views/cross applies isn't an option unfortunately for this data structure

Solution

  • Is this what you want?

    (case when Substring([Txt], 147, 10) like '%-%' 
      then '-' + Replace(LTrim(RTRIM(Substring([TXT], 147, 9))), ',', '') 
      else Replace(LTrim(RTRIM(Substring([TXT], 147, 9)))
 end) as [Open_Amount]
from db.txt

    If you want to keep only negative rows, then use:

    where substring([Txt], 147, 10) like '%-%'