February 12, 2015 at 12:43 am
Hi All,
I have a table with code and description as below
create table isin_code
(
code varchar(5),
code_desc varchar(255)
)
go
insert into isin_code values ('aaa','aäsas')
go
insert into isin_code values ('aaa','as╚as')
go
insert into isin_code values ('aaa','aâsas')
go
insert into isin_code values ('aaa','asas')
go
I want to identify the list of alt codes available in the table.
Inputs are welcome!
karthik
February 12, 2015 at 4:32 am
I have a table with code and description as below
create table isin_code
(
code varchar(5),
code_desc varchar(255)
)
...
I want to identify the list of alt codes available in the table.
Inputs are welcome!
If you need a list of alt codes available in the table, the following, a bit complicated query, should produce one for you:
SELECT code FROM isin_code
😉
Something tells me that it is not what you are after...
OK, the first thing is, if you want to see "+" inserted and displayed, VARCHAR is not right datatype. Try this:
create table #isin_code
(
code varchar(5),
code_desc Nvarchar(510)
)
go
insert into #isin_code values ('aaa',N'aäsas')
go
insert into #isin_code values ('aaa',N'as+as')
go
insert into #isin_code values ('aaa',N'aâsas')
go
insert into #isin_code values ('aaa',N'asas')
go
select * from #isin_code
Please note: not only datatype changed to NVARCHAR (double sized in order to allow for 255 chars as it will require 2 bytes per character), but also the way you should refer to string values (N at front of openning quote).
And the last one, if you really need to list distinct characters accross all values in the single column, you can use the following:
;WITH LP
AS
(
SELECT TOP(255) ROW_NUMBER() OVER (ORDER BY L1.V) P
FROM (VALUES (1),(1),(1),(1),(1),(1),(1)) L1(V)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1)) L2(V)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1)) L3(V)
)
SELECT DISTINCT SP.Single_Char
,ASCII(SP.Single_Char) As Ascii_Code
FROM #isin_code ic
CROSS APPLY (SELECT SUBSTRING (ic.code_desc,LP.P,1) FROM LP WHERE LP.P<=LEN(ic.code_desc)) SP(Single_Char)
ORDER BY Ascii_Code
February 13, 2015 at 3:48 am
Thanks EE.
is it possible to include the row number too ? Because In my original table there are around 800000 records in the table. The above query is giving only the symbol. I have modified the query to get the "Used in" Count. But Now I want to know in which line number this special character is placed.
karthik
February 13, 2015 at 5:15 am
Your question setup doesn't contain enough details to help you. If you could follow the following http://qa.sqlservercentral.com/articles/Best+Practices/61537/ providing exact ddl for table you have, good sample setup and exact expected results, then we could help you better.
February 13, 2015 at 5:19 am
expected output:
SpecialCharacter RowNum
ä 1,3
+ 2
karthik
February 13, 2015 at 6:37 am
if your table had a row number or primary key, then it could be displayed.
does your real table have it?
so isntead of this:
create table #isin_code
(
code varchar(5),
code_desc Nvarchar(510)
)
it's really this?
create table #isin_code
(
rowNum int identity(1,1) not null primary key,
code varchar(5),
code_desc Nvarchar(510)
)
Lowell
February 16, 2015 at 10:05 am
karthik M (2/13/2015)
expected output:SpecialCharacter RowNum
ä 1,3
+ 2
"something which looks a bit dodgy" is not good enough....
What is your techincal definition of "Special Character"?
Any non-english letters?
Digits?
Currency signs?
Punctuation?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply