LukeConey
asked on
SQL SERVER
having a bit of a problem in sql
i have a table and have been given a task to sort some of the fields out in the table
I have a firstname field which contains there name and all i want to have is the initial so i want to cut or trim all of the characters after the first one,, how can i do this,, ive tried to make the field smaller and isnt workin and ive also tried to use many different statements which also are not workin
another problem im having is i have a column which contains number 1-26 and i need code that will change these to letters eg
1= A
2 =B
3 =C
and so on
Please help
i have a table and have been given a task to sort some of the fields out in the table
I have a firstname field which contains there name and all i want to have is the initial so i want to cut or trim all of the characters after the first one,, how can i do this,, ive tried to make the field smaller and isnt workin and ive also tried to use many different statements which also are not workin
another problem im having is i have a column which contains number 1-26 and i need code that will change these to letters eg
1= A
2 =B
3 =C
and so on
Please help
you can change the number to sql by an easy update statement such as
UPDATE TableName
SET ColumnName = CASE ColumnName WHEN 1 THEN 'A'
WHEN 2 THEN 'B'
---------
END
But you must be sure thath your column can hold string values... check the data type
ltrim and rtrin function do not give you what you want...
AND A CODE L0KE THE ONE BELOW CAN G0VE YOIU THE 0N0T0ALS...
SUBSTRING(ColumnName, 1, 1) + SUBSTRING(ColumnName, PATHINDEX(' ',ColumnName) + 1, 1) +
ISNULL(SUBSTRING(ColumnNam e, PATHINDEX(' ',SUBSTRING(ColumnName, PATHINDEX(' ',ColumnName) + 1, LEN(ColumnName) - PATHINDEX(' ',ColumnName) + 1 ) + 1, 1) , '')
UPDATE TableName
SET ColumnName = CASE ColumnName WHEN 1 THEN 'A'
WHEN 2 THEN 'B'
---------
END
But you must be sure thath your column can hold string values... check the data type
ltrim and rtrin function do not give you what you want...
AND A CODE L0KE THE ONE BELOW CAN G0VE YOIU THE 0N0T0ALS...
SUBSTRING(ColumnName, 1, 1) + SUBSTRING(ColumnName, PATHINDEX(' ',ColumnName) + 1, 1) +
ISNULL(SUBSTRING(ColumnNam
ASKER
THANKS FOR THAT
ALSO i need to change names of job titles
for example
Proprietor has not got to become Business owner
how do i do that
ALSO i need to change names of job titles
for example
Proprietor has not got to become Business owner
how do i do that
LukeConey,
With respect, you've already asked two questions in this thread, now this is a third.
Regards,
Patrick
With respect, you've already asked two questions in this thread, now this is a third.
Regards,
Patrick
ASKER
im offering more points
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
UPDATE SomeTable
SET SomeColumn = LEFT(SomeColumn, 1)
Numbers to letters (assumes that the column to be updated is already [n][var]char data type):
UPDATE SomeTable
SET SomeColumn = CHAR(64 + CAST(SomeColumn AS int))
WHERE CAST(SomeColumn AS int) BETWEEN 1 AND 26