Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

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
0
LukeConey
Asked:
LukeConey
  • 3
  • 2
1 Solution
 
Patrick MatthewsCommented:
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
0
 
tigin44Commented:
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) , '')
0
 
LukeConeyAuthor Commented:
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Patrick MatthewsCommented:
LukeConey,

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

Regards,

Patrick
0
 
LukeConeyAuthor Commented:
im offering more points
0
 
Patrick MatthewsCommented:
For a one-off change...

UPDATE SomeTable
SET SomeColumn = 'Business owner'
WHERE SomeColumn = 'Proprietor'
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now