how to merge this query to get result

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to merge this query to get result

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-12-17 : 01:53:46

hello all,

i have 2 queries singely it is giving result but i don't know how to merge this subquery in update statement the child sub query in update query

my update query

update users set nationalID = UPS

THis is the query need to be in update query
--------
DECLARE @Value NVARCHAR(200)

SET @Value = '1Z 2Y9 589 64 9538 369 8'

WHILE @Value LIKE '%[^0-9]%'
SET @Value = SUBSTRING(REPLACE(@Value, SUBSTRING(@Value, PATINDEX('%[^0-9]%', @Value), 2), ''),0,20)

SELECT @Value

after updating result shoul come like this '128945386'

dynamically need to update all values one time basing ON UserID.suggest me

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-17 : 04:27:49

May be this........?

DECLARE @Value nvarchar(200)
UPDATE users
SET nationalId = CASE WHEN @Value LIKE '%[^0-9]%'
THEN SUBSTRING(REPLACE(@Value, SUBSTRING(@Value, PATINDEX('%[^0-9]%', @Value), 2), ''),0,20) END

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-12-17 : 05:53:40

not like that in my column records are varied not fixed to certain length in one scenario like 20 char,22char i have followed another using if condition it worked for me...

DECLARE @Value NVARCHAR(200), @count int, @tempcount int = 1
select @count = COUNT(*) from NewFakeList
print @count
while @count >= @tempcount
Begin
select @Value = UPS from NewFakeList where ID = @tempcount
WHILE @Value LIKE '%[^0-9]%'
begin
SET @Value = SUBSTRING(REPLACE(@Value, SUBSTRING(@Value, PATINDEX('%[^0-9]%', @Value), 2), ''),0,20)
-- select @Value
end
if(LEN(@Value)=7)
Begin
update NewFakeList set NationalID=@Value+'74' where ID = @tempcount
end
else if(len(@Value) >= 11)
Begin
update NewFakeList set NationalID=SUBSTRING(@Value,1,9) where ID = @tempcount
End
else
Begin
update NewFakeList set NationalID=SUBSTRING(REPLACE(@Value,' ', ''),1,9) where ID = @tempcount
end

set @tempcount = @tempcount + 1
END

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-17 : 06:22:22

You can write it in single statement

;WITH CTE AS 
(
	SELECT UserID, SUBSTRING(REPLACE(UPS, SUBSTRING(UPS, PATINDEX('%[^0-9]%', UPS), 2), ''),0,20) Value FROM NewFakeList WHERE UPS LIKE '%[^0-9]%')
)
UPDATE N 
SET n.NationalID = CASE WHEN LEN(Value)= 7 THEN Value+'74'
			WHEN LEN(Value) >= 11 THEN SUBSTRING(Value, 1,9)
			ELSE SUBSTRING(REPLACE(Value,' ', ''),1,9) END
FROM NewFakeList n 
JOIN cte c on n.userId = c.userId

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-12-18 : 00:09:00

yeah i written all the big query using if condition and everything.But u made it look simple by writing in CTE .... amazing one!!

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-18 : 00:23:10

Thank you...
Have you used this one?

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-18 : 01:03:38

Hi Mohan,


--Here am using user-defined function to get numeric part
GO
CREATE Function [dbo].[RemoveNonNumericCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
 
    While PatIndex('%[^0-9]%', @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex('%[^0-9]%', @Temp), 1, '')
 
    Return @TEmp
End
GO

-- This is your sample data
CREATE TABLE #tab( nationalId varchar(10), userid varchar(100))
INSERT INTO #tab VALUES(null, 'djn dj33 9202vkk dmdmd23') ,(null, 'KMFKF43848 JDJD9 34934903403')
SELECT * FROM  #tab

-- This is actula query. Just replace #tab with your table name
;WITH CTE AS 
(
	SELECT UserID, SUBSTRING([dbo].[RemoveNonNumericCharacters] (userid), 1, 9) Value 
        FROM #tab 
        WHERE Userid LIKE '%[^0-9]%'
)
UPDATE N 
SET n.NationalID = CASE WHEN LEN(Value)= 7 THEN Value+right('00' + convert(varchar(2),convert(int,rand()*100)),2)
			WHEN LEN(Value) >= 11 THEN SUBSTRING(Value, 1,9)
			ELSE SUBSTRING(REPLACE(Value,' ', ''),1,9) END
FROM #tab n 
JOIN cte c on n.userId = c.userId

SELECT * FROM #tab

Note: One more thing is you are not handling the case of length 8...
In the result, 33920223 is one of the userid.. this is the length of 8 only

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources