I have a table (defect ) where a column stores a text. Each line in this text represents a version. (this is clearquest database running microsoft SQL, accessed via JDBC)
For example, following data represents three versions a fix is made.
defect version_fixed
1 2015.1.1
2 2015.1.1\n2015.1.13
3 2015.1.12\n2015.1.1
4 2015.1.12\n2015.1.1\n2015.1.13
5 2015.1.13\n2015.1.10
5 2015.1.100
As you see the version is not stored in an order. It can appear anywhere.
I am interested in all rows with fix version fixed containing "2015.1.1". But my query either gets more rows or skips some
version_fixed like '%2016.1.1%' (gets row 5 as it matches the pattern)
version_fixed like '%2016.1.1\n'(does not get any thing.)
I am looking for query to get exact list for 2015.1.1
defect version_fixed
1 2015.1.1
2 2015.1.1\n2015.1.13
3 2015.1.12\n2015.1.1
4 2015.1.12\n2015.1.1\n2015.1.13
How can I query where text matches with "exact string, delimited by new line or end of text". What is the correct way to escape new line?
Side note: Current solution is to get all records(including unwanted one and then filter out incorrect results)
You could try this. It relies on Sql Server adding the newline to the string when you break the line.
create table defect( version_fixed varchar(max) )
insert into defect( version_fixed )
values ( '2015.1.1' )
, ( '2015.1.1
2015.1.13' )
, ( '2015.1.12
2015.1.1' )
, ( '2015.1.12
2015.1.1
2015.1.13')
, ( '2015.1.13
2015.1.10' )
, ( '2015.1.100' )
-- break to a new line and Sql Server will include the newline character in the string
select * from defect where version_fixed like '%2015.1.1
%' or version_fixed like '%2015.1.1'