Get data from SQL field

[Mike Bishop]

I have a view that pulls in a field, the feild has data in the following format.

A1-an987389
B21-At081l0
AA1-ath01882

how would i get the infoamtion before the "-" IE
A1
B21
AA1

the size of the data before the "-" can be anything from 1 up 5 charcters

i dont think this can be done, but if it could it could same me a weeks worth of work

thanks in advanced.

Mike

[BitBlt]

Try this:

create table dbo.myTable
(myString varchar(25))
go

insert into dbo.myTable
(myString)
values
('A1-an987389')
go

insert into dbo.myTable
(myString)
values
('B21-At081l0')
go

insert into dbo.myTable
(myString)
values
('AA1-ath01882')
go

-- And now for the piece de resistance...

select substring(myString, 1, charindex('-', myString, 1) - 1) from dbo.myTable

[Mike Bishop]

how would i put that in my view? see below pic

[BitBlt]

Instead of just using 'TestNoName' in the substring function, it looks like you'll have to fully qualify the name...look down a couple of lines in the dropdown to see 'dbo.VLP_QC_Results_Batch1000.TestNoName'. I think (just a guess) that if you use that fully qualified name everywhere inside the substring (and Charindex) you should be okay.

[Mike Bishop]

still no joy getting the error below.

[BitBlt]

Don't know what to tell you...you might want to try just coding the SQL directly rather than going through the GUI. My example from above worked when I tested it. Sorry I can't be more help.