March 7, 2018 at 3:40 pm
Hi Guys,
I have one situation where i need to update username, see the sql code first,
CREATE TABLE #TEMP
(ID VARCHAR(10),
FIRSTNAME VARCHAR(100),
LASTNAME VARCHAR(100),
USERNAME VARCHAR(50))
INSERT INTO #TEMP VALUES ('1326024','JACK','Rinse','JACK.Rinse3')
INSERT INTO #TEMP VALUES ('1326024','JACK','Rinse','JACK.Rinse3')
INSERT INTO #TEMP VALUES ('1326048','JACK','Rinse','JACK.Rinse')
INSERT INTO #TEMP VALUES ('1326048','JACK','Rinse','JACK.Rinse')
INSERT INTO #TEMP VALUES ('1328843','JACK','Rinse','JACK.Rinse1')
INSERT INTO #TEMP VALUES ('1335935','JACK','Rinse','JACK.Rinse2')
INSERT INTO #TEMP VALUES ('1350166','JACK','Rinse','')
INSERT INTO #TEMP VALUES ('1351545','JACK','Rinse','JACK.Rinse4')
INSERT INTO #TEMP VALUES ('1351548','JACK','Rinse','JACK.Rinse5')
INSERT INTO #TEMP VALUES ('1353102','JACK','Rinse','JACK.Rinse6')
INSERT INTO #TEMP VALUES ('1356524','JACK','Rinse','')
I need to update where Username is blank from most recent UserName Number see desiered output
ID FIRSTNAME LASTNAME USERNAME
1326024 JACK Rinse JACK.Rinse3
1326024 JACK Rinse JACK.Rinse3
1326048 JACK Rinse JACK.Rinse
1326048 JACK Rinse JACK.Rinse
1328843 JACK Rinse JACK.Rinse1
1335935 JACK Rinse JACK.Rinse2
1350166 JACK Rinse JACK.Rinse7
1351545 JACK Rinse JACK.Rinse4
1351548 JACK Rinse JACK.Rinse5
1353102 JACK Rinse JACK.Rinse6
1356524 JACK Rinse JACK.Rinse8
can anyone please help me to develop this?
Thanks in advance
March 7, 2018 at 3:42 pm
so far i try to find max values from the string,
select firstname,
lastname,
firstname + '.'+lastname + cast(max(test)+1 as varchar(10))as number
from
(
SELECT id,
firstname,
LASTNAME,
USERNAME,case when LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1) = '' then 0
else LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1) end as [test]
FROM (
SELECT id,
firstname,
LASTNAME,
USERNAME,subsrt = SUBSTRING(USERNAME, pos, LEN(USERNAME))
FROM (
SELECT id,
firstname,
LASTNAME,
USERNAME, pos = PATINDEX('%[0-9]%', USERNAME)
FROM #TEMP
where username <> ''
) d
) t
)a
group by a.FIRSTNAME, a.LASTNAME
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply