Creating a Unique identifer 1

[wfd1bdb]

I need to create a unique identifer from the values selected from 4 different fields. For example, I will query a db for a first name (pfname), a DOB (DOB), a gender (sex) and a Social security # (ssno).

Select pfname, dob, sex, ssno
from client

OK...I need:
the first 3 leters of the Pfname: 'nnn'
the entire dob in 'YYYYMMDD' format
the gender: 'm' or 'f'
the last 4 digits of ssno: '3958'
for a unique identifer that looks like: 'nnnyyyymmddm3958'

The example I have to work from
case when len(clm.pfname) >=3 then left(pfname,3)
else left(pfname,2) + '/' end
+ convert(varchar(12),clm.DOB,112)
+ clm.SEX
+ right(clm.GID,4),-- as [4], -- Unique Identifier

It uses a case, len, left, right and convert statements...which I don't really any experience with at this point in my professional development. So I would like some wisedom, if anyone would like to educate me and show me how to do this. I will add it to my bag of tricks for future use. Thanks ahead of time.

Bergg

 

[wfd1bdb]

Well, I made a little bit of progress.

SELECT left (PLNAME,3), convert(varchar(12),DOB,112) , SEX, right(ssno,4)
FROM CLient
group by PLNAME, DOB, SEX, ssno

results in:
OLI 19441103 F 6301


But now how do I get it in all together in one field?

 

[SQLDenis]

SELECT left (PLNAME,3)+ convert(varchar(12),DOB,112) + SEX + right(ssno,4)
FROM CLient


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[wfd1bdb]

Thank you soo much! It worked great!

 

[ESquared]

You might want to consider that some people do not have SSNs.

I'm also curious what you're using this unique identifier for. Is it something for use by humans, say on a manila file folder? Or will you use it somewhere else in the database as a foreign key?