Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Extracting data to the left of a Char(10)

Author  Topic 

crimsonwingz
Starting Member

1 Post

Posted - 2014-10-21 : 13:33:52
I have a description that is dynamically built for a part, with variables for this part split by line feeds. For example

100" widget
Color: White
Holes: Many
Material: Wood

This makes everything nice an neat on reports, etc, and the world is good. There are a lot of better ways to do this I am sure, but due to the limitations of the software we are using, this fits the bill best.

In SQL view, it LOOKS like the lines are all run together, but when you actually pull the data, it formats properly.

Enter a new need to identify just the first "line" of info. I would like to be able to query all characters left of the CHAR(10). In the example above, the results would be just 100" Widget. I have tried a couple ways using Left(field,charindex(char(10))-1 but nothing seems to get me data back. Is this even possible within a View?

Thanks for any direction you might be able to give me!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-21 : 13:48:54
I did this:


declare @x varchar(100)
set @x =
'100" widget
Color: White
Holes: Many
Material: Wood'

select left(@x, charindex(char(10), @x) -1)


and got:
quote:

(No column name)
100" widget

Go to Top of Page
   

- Advertisement -