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

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

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

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.

still no joy getting the error below.

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.