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.
| Author |
Topic |
|
david_m
Starting Member
6 Posts |
 Posted - 2015-01-15 : 11:43:29
|
| Hi All,I've been tasked with writing a report that shows just the account code of a customer who made a return. This usually isn't an issue, but the information provided, whilst in a SQL table in entered directly into from MS Access.In this the user has used an expression to concatenate the account code, name and phone number, e,g, it looks like this..CUSTOMER1,Customer Name 1, 01234 567 891CUSTOMER2,Customer Name 2, 19876 543 210NEWCUST1, New Customer 1, 07777 777 777If the account code was standard length I could get away with a LEFT statement to provide me with the details of the code, but as it's variable 1-10 i need to get everything before the first comma.It's been suggested to use SUBSTRING([Column1],0,CHARINDEX(',',[Column1]))?, but i hit an error when i use that.Any ideas? |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-01-15 : 12:02:27
|
Post the error message?BTW, what if there is no comma in the column? You'll wind up with an empty string, I believe.FWIW your expression works for me:select SUBSTRING([Column1],0,CHARINDEX(',',[Column1]))from (values ('a,b')) v(column1)returns:(No column name)a |
 |
|
|
david_m
Starting Member
6 Posts |
Posted - 2015-01-16 : 07:46:59
|
| Not to worry, i've sorted it. You point about the lack of a comma set me on the track of checking the info. I corrected 150 mistakes or so, now it works fine! :) |
|
|
|
|
|
|
|
|
Left of character data