Translatign from Access to SQL Server

[VegaLA]

Hi all,
I have created a form in Acess 2002 that will clean up the value of some fields in a table. There are spaces after the value and the value ( which is City, state as "Los Angeles,CA") itself needs to be shortened to take out the state so only the City is shown. I managed to do this in Access but I have been asked to code this in a T-SQL for SQL Server 2000. The code I have for Access is :-

SearchChar = ","

StrTake = Me.txtCTYST
StrCut = Trim(StrTake)
Me.txtState = Right(StrCut, 2)
strPosition = InStr(1, StrCut, SearchChar, 1)
StrCut = Mid(StrCut, 1, (strPosition - 1))
Me.txtCTYST = Null
Me.txtCTYST = StrCut

I have managed to get the T-SQL code to show just the state :-
RIGHT(RTRIM(CityState),2)

but how do I code it to show just the City, the value before the comma ?

Thanks in advance,
Mitch....

 

[pdx_man]

Check out the SUBSTRING function.

 

[VegaLA]

Cheers PDX,
that fits the bill !!

"select SUBSTRING(CitySt,1,LEN(CitySt)-4) AS 'City' FROM tblAddress"

 

[SQL_Hell]

Damn beaten to it, but anyway

Here is another way of doing it


select left(CitySt,patindex('%,%',CitySt)-1) from table1

 

[VegaLA]

Thanks SQL_Hell,
i'll play around with that too