Link to home
Start Free TrialLog in
Avatar of LukeConey
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
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Change first name to first character:

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
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(ColumnName, PATHINDEX(' ',SUBSTRING(ColumnName, PATHINDEX(' ',ColumnName) + 1, LEN(ColumnName) - PATHINDEX(' ',ColumnName) + 1 ) + 1, 1) , '')
Avatar of LukeConey
LukeConey

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
LukeConey,

With respect, you've already asked two questions in this thread, now this is a third.

Regards,

Patrick
im offering more points
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial