How to Perform a Simple String Mapping as Part of a T-Sql Select

How do I perform a simple string mapping as part of a t-sql select?

I think you want to use the CASE expression:

SELECT 
  CASE column1 
    when 'A' THEN 'B' 
    when 'F' THEN 'Z' 
  END 
FROM Tbl

Also note that there are two different syntaxes for it, choose one that is most appropriate for you.

Mapping field codes to Strings in a TSQL query

You want to use a case statement:

SELECT he.PATNT_PASID, [Pat_Given]+' '+[Pat_Family] AS Name,
       p.Pregnant, cca.CCDateAdmit, cca.CCDateDis, cca.CCOutcome,
       (case when p.Pregnant = 1 then 'Currently Pregnant'
             . . . 
             else '???'
        end) as PregString
From dbo.tblPhysiology p
     dbo.tblCritCareAdmit cca
     on p.CC_ID = cca.CC_ID join
     dbo.tblHospEpisode he
     on cca.HSPEP_ID = he.HSPEP_ID join
     dbo.tblDemographic d
     on he.PATNT_PASID = d.PATNT_PASID
WHERE p.Pregnant <> 2;

I also used table aliases for your query to simplify the query and changed the join to use explicit joins with the on clause.

Sql select rows containing part of string

SELECT *
FROM myTable
WHERE URL = LEFT('mysyte.com/?id=2®ion=0&page=1', LEN(URL))

Or use CHARINDEX
http://msdn.microsoft.com/en-us/library/aa258228(v=SQL.80).aspx

SQL - adding category to string value (mapping table)

You can do case statement like this

case 
    when last_error in ('user_aborted', 'login_bad') then 'user'
    when last_error in ('request_denied', 'blacklisted_by_admin') then 'risk'
    when last_error in ('timeout', 'country_not_available') then 'tech'
end as error_category

How to extract alpha or numbers from a string within a SELECT statement WITHOUT a function call

How to extract alpha or numbers from a string within a SELECT statement WITHOUT a function call?

You can't, because simple string operations like REPLACE() are also function calls. And, user-defined functions are compiled by SQL Server. They perform pretty well.

But your problem is not the function's performance itself, but rather how often you must use it. You knew that.

Here's a possible way to speed up your postcode-grinding task: put persisted computed columns on your table. You can even index them.

Here's how to do that.

Tell SQL Server to use schemabinding with your stored function. It needs to know that a table definition in your schema depends on the function. To do that add WITH SCHEMABINDING to your function definition.
```
...
RETURNS NVARCHAR(MAX)
WITH SCHEMABINDING
AS
BEGIN
...
```

Add two computed, persisted, columns to your table.

...
ALTER TABLE postcode
       ADD letters   
       AS (dbo.fn_StripCharacters(postcode, '^A-Z'))
       PERSISTED;
 ALTER TABLE dbo.postcode
       ADD numbers
       AS (CAST(dbo.fn_StripCharacters(postcode, '^0-9') AS INT)) 
       PERSISTED;

You can put indexes on the computed columns if you need them.
```
CREATE INDEX numbers ON postcode  (numbers DESC)
```
Now you can insert, update, or delete your non-computed columns as you wish. SQL Server evaluates your stored functions just once for each row as it is inserted or updated. Your functions still get evaluated, but not when you SELECT from your table.
And you can use the computed columns as you wish
```
SELECT * FROM postcode ORDER BY numbers DESC
```

Here's a db<>fiddle demonstrating this.

SQL Query Result to ListT mapping

Make a class Phone and change the User property accordingly:

public class Phone
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public string Number { get; set; }
}

public class User
{
    // (etc)
    public List<Phone> Phones { get; set; }
    // (etc)        
}

Make a PhoneRepository:

public class PhoneRepository
{
    public List<Phone> LoadPhonesFromId(IEnumerable<int> ids)
    {
       // write a query with the ids
       // SELECT * FROM Phones WHERE id IN (@id1, @id2, ...)
       // execute the query
       // convert the results to Phones and put them in a List<Phone>
       // return the list
    }
    public List<Phone> LoadPhonesFromUserId(IEnumerable<int> userIds)
    {
       // write a query with the userIds
       // SELECT * FROM Phones WHERE userId IN (@userId1, @userId2, ...)
       // execute the query
       // convert the results to Phones and put them in a List<Phone>
       // return the list
    }
}

And a UserRepository:

public class UserRepository
{
    public List<User> LoadUsersFromUserIds(IEnumerable<int> userIds)
    {
       // write a query with the userIds
       // SELECT * FROM User WHERE id IN (@id1, @id2, ...)
       // execute the query
       // convert the results to Users and put them in a List<User>
       // return the list
    }

    public void IncludePhones(IEnumerable<User> users)
    {            
        var phones = PhoneRepository.LoadPhonesFromUserId
            (users.Select(x => x.Id));

        for each(var user in users)
        {
            user.Phones = phones
                .Where(x => x.UserId == user.Id)
                .ToList();
        }
    }
}

You can greatly expand and improve on this pattern with, for example, custom filter parameters (instead of different functions for the various filters) and property setters that make sure the user and user.Phones keep referring to the same userID, but that is sort of beyond the scope of your question.