query - SQL Server Forums

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

query

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-09 : 06:55:31

Hi

I have data in one of the col as unix file per 'lrwxrwxrwx' i want to
replace it with 777 in the above case.
some thing like below


rwx  r-x  ---
111  101  000

how can i do this, then I need to represent
111=7
101=5
000=0.

if first char is '-' then I need to sub with 666 for file
if first char is 'd' then I need to sub with 777 for dir
if first char is 'l' then I need display it as symbolic link

-Neil

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-09 : 07:30:00

Here is a starter. I have NO idea what the last part means with "sub".

DECLARE	@Data CHAR(10) = 'lrwxrwxrwx'

SELECT	CAST(CASE WHEN SUBSTRING(@Data, 2, 1) = '-' THEN 0 ELSE 4 END
	+ CASE WHEN SUBSTRING(@Data, 3, 1) = '-' THEN 0 ELSE 2 END
	+ CASE WHEN SUBSTRING(@Data, 2, 1) = '-' THEN 0 ELSE 1 END AS CHAR(1))
	+ CAST(CASE WHEN SUBSTRING(@Data, 5, 1) = '-' THEN 0 ELSE 4 END
	+ CASE WHEN SUBSTRING(@Data, 6, 1) = '-' THEN 0 ELSE 2 END
	+ CASE WHEN SUBSTRING(@Data, 7, 1) = '-' THEN 0 ELSE 1 END AS CHAR(1))
	+ CAST(CASE WHEN SUBSTRING(@Data, 6, 1) = '-' THEN 0 ELSE 4 END
	+ CASE WHEN SUBSTRING(@Data, 9, 1) = '-' THEN 0 ELSE 2 END
	+ CASE WHEN SUBSTRING(@Data, 10, 1) = '-' THEN 0 ELSE 1 END AS CHAR(1))

N 56°04'39.26"
E 12°55'05.63"

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-09 : 08:18:39

Thanks SwePeso :)

Sorry previous post was not complete ...

I have table which has unix file permission col e.g.
Tab ( col1 v2(10))


Col1
lrwxrwxrwx
drwxr-xr-x
-rw-r--r--
-rw-------

I want to convert it to values to 777 second 755 which is ( 111 101 101) ...and then want to subtract from 777 and display the result

based on the first char if it is 'l' I want to display it as 'symbolic' if not then I want to subtract it with 777 and display the results ( under column header umask)

using of substring with Case etc should be fine

-Neil

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-09 : 08:35:53

[code]DECLARE @Sample TABLE
(
Data CHAR(10) NOT NULL
);

INSERT @Sample
(
Data
)
VALUES ('lrwxrwxrwx'),
('drwxr-xr-x'),
('-rw-r--r--'),
('-rw-------');

-- Solution
SELECT Data,
CAST(SIGN(p1.a) AS CHAR(1)) + CAST(SIGN(p1.b) AS CHAR(1)) + CAST(SIGN(p1.c) AS CHAR(1)) + ' '
+ CAST(SIGN(p2.a) AS CHAR(1)) + CAST(SIGN(p2.b) AS CHAR(1)) + CAST(SIGN(p2.c) AS CHAR(1)) + ' '
+ CAST(SIGN(p3.a) AS CHAR(1)) + CAST(SIGN(p3.b) AS CHAR(1)) + CAST(SIGN(p3.c) AS CHAR(1)) AS Symbolic,
CAST(p1.a + p1.b + p1.c AS CHAR(1)) + CAST(p2.a + p2.b + p2.c AS CHAR(1)) + CAST(p3.a + p3.b + p3.c AS CHAR(1)) AS Original,
RIGHT('000' + CAST(777 - 100 * p1.a - 100 * p1.b - 100 * p1.c - 10 * p2.a - 10 * p2.b - 10 * p2.c - p1.a - p1.b - p1.c AS VARCHAR(3)), 3) AS Reversed
FROM @Sample AS s
CROSS APPLY (
VALUES (
CASE SUBSTRING(Data, 2, 1)
WHEN '-' THEN 0
ELSE 4
END,
CASE SUBSTRING(Data, 3, 1)
WHEN '-' THEN 0
ELSE 2
END,
CASE SUBSTRING(Data, 4, 1)
WHEN '-' THEN 0
ELSE 1
END
)
) AS p1(a, b, c)
CROSS APPLY (
VALUES (
CASE SUBSTRING(Data, 5, 1)
WHEN '-' THEN 0
ELSE 4
END,
CASE SUBSTRING(Data, 6, 1)
WHEN '-' THEN 0
ELSE 2
END,
CASE SUBSTRING(Data, 7, 1)
WHEN '-' THEN 0
ELSE 1
END
)
) AS p2(a, b, c)
CROSS APPLY (
VALUES (
CASE SUBSTRING(Data, 8, 1)
WHEN '-' THEN 0
ELSE 4
END,
CASE SUBSTRING(Data, 9, 1)
WHEN '-' THEN 0
ELSE 2
END,
CASE SUBSTRING(Data, 10, 1)
WHEN '-' THEN 0
ELSE 1
END
)
) AS p3(a, b, c)[/code]

N 56°04'39.26"
E 12°55'05.63"

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-09 : 08:43:42

Thanks Peso,

I am getting the result in decimal, but I want it to be displayed in octane
excluding data for 'l' if first char, I want to display it as 'symbolic' in output else result of 777- Value in Octane

e.g. any value subtract from 777 we will get the +ve value

777
-505
----
202

the out put should be something like above..

Thanks..

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-09 : 09:00:47

Hi Swepeso,
your query is giving the correct value, I just need Reversed Column value from your query with little modification, When ever we have first char as 'l' then it should Display 'Symbolic'

-Neil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources