sql query to dynamically generate value

Question

Hi 
I have a table with data as follows:
DECLARE	@tmpV table (
   ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
   [Desc] varchar(500),
   descID varchar(5) NULL
)

INSERT INTO @tmpV ([Desc])
SELECT 'City of Franklin'
UNION
SELECT 'City of Frankston'
UNION
SELECT 'City of Fradericksburg'
UNION
SELECT 'City of Freeport'
UNION
SELECT 'City of Freer'
UNION
SELECT 'City of Friendswood'
UNION
SELECT 'City of Friona'
UNION
SELECT 'City of Frisco'
UNION
SELECT 'City of Fritch'
UNION
SELECT 'City of Frost'
UNION
SELECT 'City of Fruitvale'
UNION
SELECT 'City of Fulshear'


select * from @tmpV

DescID has to be 5 characters and dynamically generated.
SO, ‘City of Franklin’  ID should be 
a.	First letter of each word. In above case C from City, O from of, F from Franklin. So, it will be COF (only 3 letters). 
b.	ID has to be 5 letters. So, based on this rest of the 2 letters should come from last word. In this case from last word ‘Franklin’. SO, F is already used. Ra should be used. So, ID becomes COFRA
Next should go to next row or record which is ‘City of Frankston’ With above rules this is COF + ra from Frankston. So ID again becomes COFRA. Since this ID has to be unique replace last letter with 1. So, it should be COFR1
Next row ‘City of Fradericksburg’ – COR + ra from ‘Fradericksburg’. It becomes COFRA. It should be created as COFRA2
 When it reaches double digit like COFR10, then one more letter should be replaced like COF10.

Answer 1

Hi @Spunny

Try this query: code.txt

Output:

If you want to return a character expression with lowercase character data converted to uppercase, you could use the UPPER function.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.